PowerPivot SUMIF type Calculated Column ERROR

Hello All,

I thought this was pretty straight forward in PowerPivot, but I am missing something.

I have a basic table (I thought) where I have several columns of product attributes, 1 column with YEAR, and a Key Figure (quantity). It is set up very simply for pivot tables.

I want to be able to have a calculated column that SUM's the Key Figure (quantity) for each of the product attributes and year combinations.

Here is how the data structure looks:

[CUSTOMER] | [ATTRIBUTE 1] | [ATTRIBUTE 2] | [ATTRIBUTE 3] | [ATTRIBUTE 4] | [YEAR] | [QUANTITY] | [Calculated Column - Total Quantity filtered to attributes in row]

For each combination of attributes and unique year (not customer) I want to see the sum of the quantities.

You can see the attached dummy data (not in PowerPivot) for what I mean. My real data set is much bigger as you would imagine.

I tried =CALCULATE(SUM([QUANTITY]),[ATTRIBUTE 1],[ATTRIBUTE 2],[ATTRIBUTE 3],[ATTRIBUTE 4],[YEAR]) in a Calculated Column.
but I keep getting errors. =SUM([QUANTITY]) works just fine on it's own. But I need to find the sum of all the quantity for the relevant attributes + year...

This is just 1 step in my project and I am stuck now.

Any thoughts? Am I just missing understanding the syntax for DAX?

Thanks,
Matt

Hello Rory,

sorry for the delay, had to go on a long business trip at the last minute.

I need this because it is just one of the many calculations in the powerpivot I need to make and my data set is pretty large. For example, I have to make a second that calculates the weight of the row Quantity...but I can only do that if I have the pre-filtered sum of that total quantity based on my above attributes+year.

Thoughts?

Thanks,
Matt