Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
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 =
Solved! Go to 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.
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
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.
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] )
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.
@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?
User | Count |
---|---|
118 | |
66 | |
65 | |
56 | |
50 |
User | Count |
---|---|
181 | |
85 | |
67 | |
61 | |
53 |