Table totals showing wrong values

Hi   Justas4478 

first of all great that irt worked and sure I try to explain it as easy as possible.

lets start with your formula:

SUMX(
   DIM_ITEM,
   'DIM_ITEM'[UNIT_PURCHASE_COST] * CALCULATE(SUM('VAL_WHSE_ONHAND_6AM'[ONHAND]))
)I used SUMX on the DIM table. This means that we iterate through each line-item of this table.

for each line item we do the multiplication of field one and field two. But why do we need CALCULATE and SUM ofr the second and not for the first on. This is because [UNIT_PURCHASE_COST] is part of the DIM_ITEM table and Power BI gets for each line of DIM_ITEM one specific value in the column [UNIT_PURCHASE_COST].

In the second parameter this is not the case because is part of another table which is linked to DIM_ITEMS. This is why you need an aggregation function like SUM(). BUt as you have seen this was not enough. We also needed CALCULATE to get the right result. This is because SUM by default ignores row context, in this case the row context given by the iteration of DIM_ITEM. But by surrounding it with CALCULTE() you tell Power BI to apply row context and this is why you get the right result in the end.

Best regards

Michael

-----------------------------------------------------

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Appreciate your thumbs up!

@ me in replies or I'll lose your thread.

-----------------------------------------------------

LinkedIn