Final Sum Total for measure not matching up with individual row values

[csdalvi]

Hello,

I am having an issue where the final measure total shown at the bottom of the visual is not matching up with the sum of the individual rows for the columns: "Prev MTD Shipment" and "MTD Shipment". I have attached an example of the visual where the error is occurring.

I am trying to identify the month to date shipments for the given month in "MTD Shipment" and the Previous months month to date shipment for the same day in "Prev MTD Shipment". I used the below dax measures to get the results.

Prev MTD Shipment =

CALCULATE(sum('Cust Order Details'[Ordered Qty MT]),

                    dateadd(

                                   FILTER(

                                               DATESMTD('Cust Order Details'[confirmed_delivery_date]),

                                               'Cust Order Details'[confirmed_delivery_date]<TODAY()

                                               ),

                                    -1,

                                    month

                                    )

                          )

MTD Shipment =

CALCULATE(sum('Cust Order Details'[Ordered Qty MT]),

                    dateadd(

                                  FILTER(

                                              DATESMTD('Cust Order Details'[confirmed_delivery_date]),

                                              'Cust Order Details'[confirmed_delivery_date]<TODAY()

                                             ),

                                  0,

                                  month

                                  )

                     )

I would appreciate any help regarding this issue.

Regards

[v-rzhou-msft]

Hi @csdalvi 

Could you tell me if your problem has been solved? If it is, kindly Accept it as the solution. More people will benefit from it. Or you are still confused about it, please provide me with more details about your table and your problem or share me with your pbix file from your Onedrive for Business.

Best Regards,

Rico Zhou

[v-rzhou-msft · Accepted solution]

Hi @csdalvi 

Measure will show you an aggregation result.

Due to I don't know your data model, I will give you some advice. I hope they can help you.

Please build a new measure by your old measure and have a try.

New_Prev MTD Shipment = Sumx(Table,[Prev MTD Shipment])

New_MTD Shipment  = Sumx(Table,[MTD Shipment])

Sum your old measure may be useful to show correct result in Total.

Or you may need to use if and hasonevalue function to show correct value by dax.

New_Prev MTD Shipment = 
VAR _Total = ...
Return
If(Hasonevalue(Table[Group Customer]),[Prev MTD Shipment], _Total)

New_MTD Shipment = 
VAR _Total = ...
Return
If(Hasonevalue(Table[Group Customer]),[MTD Shipment], _Total)

If this reply still couldn't help you solve your problem, please provide me a sample by your Onedrive for Business.

This may make it easier for me to solve your problem.

Best Regards,

Rico Zhou

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

[ToddChitt]

Do you have a Date dimension hooked in? If your fact data does not contain at least one row for each date in the calendar, you may get bad results. Best to have a Date dimension joined to the Fact table.

Then you MTD calculation becomes almost trivial, and build your measures one on top of another:

Shipment Total = SUM ( SUM ( 'Cust Order Details' [Ordered Qty MT] ) 

MTD Shipment = TOTALMTD ( [Shipment Total], 'Dates' [Date] )

[csdalvi]

I had connected the fact table connecting the "confirmed_delivery_date" to another date dimension table, but it did not correct the issue.

I also tried out the TOTALMTD formula but it gave the same values and issues as the previous formula which I wrote.

[Ashish_Mathur]

Hi,

Share the link from where i can download your PBI file.

[ebeery]

@csdalvi , the value in the "Total" row should not necessarily be expected to equal the sum of the values in the rows above it - this is not the way measures work in Power BI visualizations.  The measure is evaluated separately in the particular context of each cell of the visual.

There are numerous helpful posts already in the forum which explain how to remedy the issue.

One particularly good one is here:
https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376

[csdalvi]

Thanks for the article. Could you suggest the issue in the formula I have written?