Calculate

[Centre13]

Hi - I have a calulate expression which performs at a row level. I want to sum this expression at a product level. Any ideas how to do this?

calculate(min(Table1[Absolute USD Detail]),Table1[Bank Levy BS]="Derivatives",allexcept(table1,Table1[ADAC Unit ID],table1[Netting Set ID])) - this is the row level calc. How do I sum it up by product level.
</SPAN>
Many thx for your help.</SPAN>

 

[scottsen]

In general it should "just work" which makes me think... maybe your above calculation is a "calculated column" (vs a measure, also called calculated field) ?

In general, this sort of measure would also probably do what you want?
=SUMX(Products, [YourMeasure])

 

[Centre13]

Hi Scott - thanks for the reply.
Yes it is a calculated column. I made the below adjustment but it seems to be adding the minimums for all products. Also, appears sumx requres a table reference and not a column reference so I cant seem to isolate the products.

=sumx(table1,calculate(min(Table1[Absolute USD Detail]),Table1[Bank Levy BS]="Derivatives",allexcept(table1,Table1[ADAC Unit ID],Table1[Cluster Name],Table1[Netting Set ID])))

 

[scottsen]

Well, in general... you are going to WANT a measure, not a calc column... so that this can be dynamic. eg, if you filter out Store 101, it adjusts properly.

That said, for a calc column, the general formula is:

=CALCULATE([SomeMeasure], FILTER(MyTable, MyTable[SomeColumn] = EARLIER[SomeColumn)))

Where in your case, SomeColumn is going to be your product level.

 

[Centre13]

Scott - this is great. Works a treat. Thx