Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
csdalvi
Frequent Visitor

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

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.

 

PowerBI MTD Measure Issue.JPG

 

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
1 ACCEPTED SOLUTION
v-rzhou-msft
Community Support
Community Support

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. 

 

View solution in original post

7 REPLIES 7
v-rzhou-msft
Community Support
Community Support

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
Community Support
Community Support

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
Super User
Super User

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] )

 

 




Did I answer your question? If so, mark my post as a solution. Also consider helping someone else in the forums!

Proud to be a Super User!





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.

Hi,

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
ebeery
Solution Sage
Solution Sage

@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

 

 

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

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.