Table totals showing wrong values

[Justas4478]

Hi I have a measure multiplies one column with other to do the multiplication. The result values are correct but when I look at the totals at the bottom of the table they are showing much bigger numbers. When I filter for singe item totals match to that item but whent there are more than 1 item in the table totals starting to show bigger than values for those items.

This is the DAX measure that I am using. 

PICKS_VALUE £ =

CALCULATE(SUMX(DIM_ITEM,'DIM_ITEM'[UNIT_PURCHASE_COST]) * SUMX(VAL_WHSE_ONHAND_6AM,'VAL_WHSE_ONHAND_6AM'[ONHAND]))

 

The totals expected for the PICKS_VALUE £ should be 128064 and for PICKS_VOLUME 666.05

Thanks.

[Mikelytics]

Hi @Justas4478 

right ... can you please try:

PICKS_VALUE £ =

SUMX(
   DIM_ITEM,
   'DIM_ITEM'[UNIT_PURCHASE_COST] * SUM('VAL_WHSE_ONHAND_6AM'[ONHAND])
)

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

[Justas4478]

@Mikelytics No error this time but totals still wrong. I guess the totals are looking at more items than just the ones on the table maybe.

[Mikelytics · Accepted solution]

HI @Justas4478 

sorry, I identified another missing piece (i think". Please try:

PICKS_VALUE £ =

SUMX(
   DIM_ITEM,
   'DIM_ITEM'[UNIT_PURCHASE_COST] * CALCULATE(SUM('VAL_WHSE_ONHAND_6AM'[ONHAND]))
)

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

[Justas4478]

@Mikelytics It worked. Thank you. Could you explain what was wrong and why solution you provided worked so I could understand and learn?

[Mikelytics]

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:

PICKS_VALUE £ =

CALCULATE(

   SUMX(DIM_ITEM,'DIM_ITEM'[UNIT_PURCHASE_COST])

   * 

   SUMX(VAL_WHSE_ONHAND_6AM,'VAL_WHSE_ONHAND_6AM'[ONHAND])

)

 

The problem was here, that in the first part you itertate through all Dim_Items and get a result 

in the next step you again go through all items and get a second result. 

then the first and the second result have been multiplied.

 

So you DID NOT multiple by row and then took the SUM of all results

you DID Iterate first through each table separately and multiplied the results of each table afterwards. This is why you only got the right result when choosing one value.

 

then lets look on my result;

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

[Justas4478]

@Mikelytics Thank you it is really good explanation I was not aware that sum ignores row part in the tables. Thanks again.

[Mikelytics]

Hi @Justas4478 

Can you please try the following:

PICKS_VALUE £ =

SUMX(
   DIM_ITEM,
   'DIM_ITEM'[UNIT_PURCHASE_COST] * 'VAL_WHSE_ONHAND_6AM'[ONHAND]
)

If this does not work then I would need to see your data model, especially how the two tables relate to each other and which columns from which table are used in the visual.

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

[Justas4478]

@Mikelytics I got this message when I tried your formula. 

This is the realationships of the tables. They are connected using ITEM ID.

Let me know if you need more information.