Incorrect Measure Total

[NT2510]

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

[Anonymous]

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

[Anonymous · Accepted solution]

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

[parry2k]

@NT2510 can you share a sample pbix file, I need t see the model and what you are doing with those measures. It should work.

[NT2510]

Thank you for your help. I think the 42% is showing correct since its at quarter level.

[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.⚡

[NT2510]

Thank you. I tried above solution, but it gives me same result as Ratio which is 300% in total.

[parry2k]

@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.⚡

[NT2510]

It wont work because numerator and denominator are measures and not the columns from the table.