Calculate the sum of a measure

[Tasos]

Hello,

I need help with something I've spent a whole day to calculate.

I need to calculate (and not just to show) the total sum of a measure.

In the screenshot below, each SKU was sold multiple times during a period.

I have calculated the total number of cases (for example for SKU AB000ACL04-ELCL, Cases=10+20+30+6=66).

I have a separate table with the CartonQuantity, meaning the units per case per SKU (for the same SKU, there are 34Units per case).

Finally, I have multiplied the #of cases with the CartonQuantity (66*34=2,244 Units)

What I need is a measure that calculates and returns the total Units (2,244+0+0+0+220+80+...=X).

I could have done it as a column, however, I need it as a measure as I want it to be dynamic and be updated when I apply filters (period/ specific SKUs/etc)

Thank you

[Anonymous]

Hi Tasos,

Got it. Keep SKU's on the dimension level. So that, You have mapping with your fact table and the data will be filtered out based on the selection.

SKU - - > your table (SKU column) 

Your filter should be from the dimension and not from fact.

Pradeep

[toddpbi · Accepted solution]

Hi there Tasos,

Try and use an iterator function like SUMX. SUMX creates a filter context by itterating every row of the table with row context, multiplies the column values and then sums them up.

Try writing the following measure (assuming your carton quantity table contains a single row for every SKU).
Make sure there is a relationship between SKU between both of your tables.

UnitsAsCases =
SUMX ( 'TableName', 'TableName' [ Cases ] * RELATED ( 'TableName' [CartonQuantity] )
)

[Tasos]

@toddpbi, 

one more question; although what you proposed worked in my previous case, I don't have the output I want in a supplementary one.

I want to sum all these eachords that the sum of cartonords and the sum of palords are 0.

In my first attempt, it returns the difference between the eachords and the other two values (eachords-cartonords-palletordrs).

The second one returns the correct value per line (0 for the first line, 2,508 for the second, etc), however, the total is incorrect. 

Can you also give a hand on that? 

Thanks

[Tasos]

@toddpb, Thanks for the quick reply. It seems like it is working.

@Anonymous, not sure I have understood your reply.