Forum Discussion
Incorrect Measure Total
Hello,
I am trying to figure out how to show correct totals in below table.
Denominator and Numerator are two different measures and Old_Ratio just calculates as Numerator/denominator. For Q2, the old Ratio shows 42% which is not correct, it should show 100% and exclude any rows where numerator or denominator is blank.
I tried fixing it by using SUMX where measure is as:
Ratio = SUMX(Values(Year), Divide(Numerator,Denominator))
But now it shows sum of the % values as 200% and 300%. Is there any way to fix this issue.
Thanks
Hi NT2510 ,
You can create another new measure as below to replace the measure [Ratio](Ratio=Divide(Numerator,Denominator)) and put it on the matrix. Please note that replace the table name "Table" referenced in the following formula with the actual table name you applied to the matrix.
New Measure = SUMX ( GROUPBY ( 'Table', 'Table'[Year], 'Table'[Quarter], 'Table'[Month] ), [Ratio] )If the above one can't help you get the correct result, you can refer the method in the following links to resolve it.
Why Your Total Is Incorrect In Power BI - The Key DAX Concept To Understand
Dax for Power BI: Fixing Incorrect Measure Totals
Best Regards
8 Replies
- parry2k
Super User
NT2510 change it to this
Ratio = SUMX(Values(Year), Divide(CALCULATE(SUM(Numerator)),CALCULATE(SUM(Denominator))))Check my latest blog post Comparing Selected Client With Other Top N Clients | PeryTUS I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!
⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡
It wont work because numerator and denominator are measures and not the columns from the table.
- parry2k
NT2510 sorry was not sure if those are measures:
Try this:
% = SUMX ( SUMMARIZE ( 'Rate', 'Rate'[Year], 'Rate'[Qtr], "@TotalNum", [Total Num], "@TotalDen", [Total Den] ), DIVIDE ( [@TotalNum], [@TotalDen] ) )Check my latest blog post Comparing Selected Client With Other Top N Clients | PeryTUS I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!
⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡
Thank you. I tried above solution, but it gives me same result as Ratio which is 300% in total.
Thank you for your help. I think the 42% is showing correct since its at quarter level.
Hi NT2510 ,
You can create another new measure as below to replace the measure [Ratio](Ratio=Divide(Numerator,Denominator)) and put it on the matrix. Please note that replace the table name "Table" referenced in the following formula with the actual table name you applied to the matrix.
New Measure = SUMX ( GROUPBY ( 'Table', 'Table'[Year], 'Table'[Quarter], 'Table'[Month] ), [Ratio] )If the above one can't help you get the correct result, you can refer the method in the following links to resolve it.
Why Your Total Is Incorrect In Power BI - The Key DAX Concept To Understand
Dax for Power BI: Fixing Incorrect Measure Totals
Best Regards
Hi NT2510 ,
Whether your problem has been resolved? If yes, please mark the helpful post as Answered? It will help the others find the solution easily if they face the same problem with you. Thank you.
Best Regards
