DAX Calculated column ALLEXCEPT v ALL / Values;

Workbook included.

Hi,

I have a measure ;

Total Item:=CALCULATE([Total],ALL(Table1),VALUES(Table1[Item]) )

which give a total for items, I would like to replicate this as a calculated column using ALL and Values.

I have got it to work uisng ALLEXCEPT ;

Please Login or Register  to view this content.

but I cannot get ALL / VALUES to work ; I have tried ;

Please Login or Register  to view this content.

which I thought would work the same, but I just get an overall total and I've also tried; ;

Please Login or Register  to view this content.

and I'm not quite sure how this in calculating?

How can I use ALL and VALUES to get the result I want?

Richard.

I can't imagine why you would, but it would need to be:

=CALCULATE([Total],ALL(Table1[Date]),ALL(Table1[Units]),VALUES(Table1[Item]) )

in order to override the row context filter on the Units column - which is why you get the answers you do with your CALCULATE([Total],ALL(Table1[Date]),VALUES(Table1[Item]) ) version.

VALUES is an iterator and in the row context of a calculated column, it returns all the Item values for each row of the table, so it actually has no effect when combined with your ALL(Table1).