Sum of numbers does not equal the total

DAX DAX Calculations
1

Hi,

Averages Test.pbix (23.3 KB) Data.xlsx (22.7 KB)

Attached are two files. One is the source data which includes the issue via pivot table (just incase this might help) and the other is the PBIX file showing the dax and the visuals / issue.

Per the PBIX file I am trying to do the following:

  1. Per week calculate the average price for each customer
  2. Less the average price per week for all customers
  3. and Multiply the diff by the quantity per customer (If no quantity it should be zero)

In Visual 1 each week appears to be correctly calculated but the sum of these do not equal the Total. This is a problem for me and causing me a massive headache.

E.g for customer B = -908.21-593.03-1120.09+24.55+500.63= -2096.15 not -2390.96 per the visual

Hopefully this is enough for everyone to go on and I am just making a stupid mistake. I am being chased on this so any feedback is massively appreciated. Thanks Guys

2

Hi @Hitman.

Have you tried the Fix Incorrect Totals pattern?

You may also get some insights from this thread

Hope this helps.
Greg

3

Hi @Hitman,

Can you give this a go.
I’m a bit concerned with your model though… it only contains the single fact table no date- or client dimension tables…

Variance Amount v2 = 
VAR vTable = 
    ADDCOLUMNS(
        ADDCOLUMNS(
            SUMMARIZE( Source_Data, Source_Data[Week], Source_Data[Customer] ),
            "@AvgWeek", [All Customers Weekly Average Price],
            "@AvgCust", [Only Customer Weekly Average Price],
            "@Qty", [Total Quantity] ),
        "@Variance", IF( [@Qty] >0, ([@AvgCust] - [@AvgWeek])*[@Qty], 0 )
    )
RETURN

SUMX( vTable, [@Variance] )
1 Like
4

HI All- Thanks

I will try Melissa’s suggestion to see if that works and come back shortly . Re the model it is not the actual file i am working with and was just a quick / basic example to illustrate my issue as not able to share confidential data. No date table or dimension tables needed as figured the more simple the easier to display the issue. Speak soon…

5

Melissa,

That has worked perfect per visual 3 attached- Thanks

I am still confused why this has worked. Is it because by forcing it into a table it forces the total to understand what it should be the total of?

Averages Test.pbix (24.4 KB)

6

I have just read Greg’s post in more detail and that is the case. Thank you guys. Will spend a bit more time going over this but guess this was another hurdle to learn in the PBI world

7

In the matrix visual there is context coming from Week and Customer for each ‘cell’ except the Totals. There is no filtering happening on both dimensions in the Total fields, that’s where the virtual table comes in. Sam has done a lot of great videos on fixing totals and so on, just work through them - it will become more clear.

8

Hi - Please can you recommend one of these videos. Nothing was jumping out at me

Also not sure if you are aware but when you receive an email. The following button "image

Resulted in the page

image
image776×364 8.94 KB

9

Once again though guys - Massive thanks for helping resolve this. I am going back to my master version to apply the changes but that had me stumped…

Cheers

10

https://forum.enterprisedna.co/t/why-your-total-is-incorrect-in-power-bi-the-key-dax-concept-to-understand/5108

https://forum.enterprisedna.co/t/fixing-errors-with-your-totals-efficiently/4948

https://forum.enterprisedna.co/t/fixing-complex-total-errors-dax-formula-concepts/598

Think this should get you started :wink:

11

Greg - Big thanks to you too sir. Had a bit more time to go through your email and that is a massive help. Thanks you!

12

Thanks for your kind words … you’re more than welcome. I’m glad you found the resources helpful. Greg