Forum Discussion
Power BI total not displaying properly
I am new to Power BI. I have a measure which uses formula to compute Revenue change price $. The individual rows line by line are all correct, except that the "Total" displays -76,814,641.80 for column Revenue change Price $. The actual number should be -61,508.2. The total for this column is doing the (PY total price $ minus CY total price $) * PY total volume, resulting in (10201.31-12,277.38) * 37000 = -76,814,641.80.
I tried creating a calculated column instead of a measure but keep getting loop issues in the formula. Is there a simple way to have the Revenue Price change $ column just sum the records instead of how it is calculated now? Should I contine using a calculated measure or should use a calculated column rather? Can someone help with the formula please? Thanks!
Note:
1.Price $ variance = CY Total Price $ - PY Total Price $
2. Also attached excel screenshot for reference where the total (sum) is displayed as -61,508 for column K (Revenue change price $).
Hi Anonymous ,
Thanks for reaching out to the Microsoft fabric community forum.
Power BI can often miscalculate totals because it lacks row context when evaluating measures. To ensure accurate total calculations, especially when working with groups like categories or months, it's effective to use SUMX in combination with SUMMARIZE. This approach instructs Power BI to iterate over each group and apply the logic at the correct granularity. Additionally, using CALCULATE helps enforce context transition, which is essential for maintaining accurate row-level calculations at the total level.
Use SUM when you need to aggregate a single column without any row-by-row logic. In contrast, use SUMX when your calculation depends on evaluating each row individually for example, computing (PY Price – CY Price) × Volume for each item. If your totals appear incorrect, it’s often because Power BI is applying your formula to aggregated values rather than summing row-level results. To fix this, wrap your logic inside SUMX, optionally combined with SUMMARIZE or VALUES, to ensure proper row-wise evaluation and accurate totals.
Modify your DAX measure and check the TotalRevenue Change Price $ = SUMX ( SalesData, (SalesData[PY Total Price $] - SalesData[CY Total Price $]) * SalesData[PY Total Volume] )
If this post helps, then please consider Accepting as solution to help the other members find it more quickly and consider giving a KUDOS. Feel free to reach out if you need further assistance.
Thank youThanks for the detailed explanation! I was having trouble posting this initial post, and since this was not getting posted, I had to create a new post about the issue, and it got resolved. Please see below:
Your answer makes so much sense, about using SUMMARIZE or VALUES. Thank you much for putting the time and efforts on this, appreciate it!!
7 Replies
- PhilipTreacy
Super User
Hi Anonymous
See if this helps Fixing Incorrect Totals in DAX • My Online Training Hub
If you are still stuck, please supply your data, preferably in PBIX file.
Regards
Phil
Hi Anonymous ,
Thanks for reaching out to the Microsoft fabric community forum.
Power BI can often miscalculate totals because it lacks row context when evaluating measures. To ensure accurate total calculations, especially when working with groups like categories or months, it's effective to use SUMX in combination with SUMMARIZE. This approach instructs Power BI to iterate over each group and apply the logic at the correct granularity. Additionally, using CALCULATE helps enforce context transition, which is essential for maintaining accurate row-level calculations at the total level.
Use SUM when you need to aggregate a single column without any row-by-row logic. In contrast, use SUMX when your calculation depends on evaluating each row individually for example, computing (PY Price – CY Price) × Volume for each item. If your totals appear incorrect, it’s often because Power BI is applying your formula to aggregated values rather than summing row-level results. To fix this, wrap your logic inside SUMX, optionally combined with SUMMARIZE or VALUES, to ensure proper row-wise evaluation and accurate totals.
Modify your DAX measure and check the TotalRevenue Change Price $ = SUMX ( SalesData, (SalesData[PY Total Price $] - SalesData[CY Total Price $]) * SalesData[PY Total Volume] )
If this post helps, then please consider Accepting as solution to help the other members find it more quickly and consider giving a KUDOS. Feel free to reach out if you need further assistance.
Thank youThanks for the detailed explanation! I was having trouble posting this initial post, and since this was not getting posted, I had to create a new post about the issue, and it got resolved. Please see below:
Your answer makes so much sense, about using SUMMARIZE or VALUES. Thank you much for putting the time and efforts on this, appreciate it!!
