topic Re: Ratio Calculation with SUMMARIZE in DAX Commands and Tips

Ratio Calculation with SUMMARIZE

RedaBi — Wed, 12 Jun 2024 21:13:44 GMT

I encountered an issue with calculating the ratio in RLS. When RLS is enabled, the ratio calculation does not work correctly. To work around this problem, I created a separate table to calculate the sales ratio and linked it to a date table. However, the result is not correct. The amount calculation is correct, but the ratio is incorrect. I tried multiplying the result by 100 to obtain the ratio as a percentage, but without success. Here is the measure used,
Could you help me identify where the problem lies and how to correct the ratio calculation?

Ratio = SUMMARIZE( 'Prélevement', 'Prélevement'[Date Opération], 'Prélevement'[Cd Dist], 'Prélevement'[Distrib], 'Prélevement'[Facial], "Total Ventes", SUM('Prélevement'[Facial]), "Total Qte", SUM('Prélevement'[Qte Livree]), "Ratio Vente", DIVIDE(SUM('Prélevement'[Facial]), CALCULATE(SUM('Prélevement'[Facial]), ALL('Prélevement')))*100 )

Re: Ratio Calculation with SUMMARIZE

lbendlin — Thu, 13 Jun 2024 02:01:02 GMT

Most likely you wanted to use ALLSELECTED rather than ALL.

REMOVEFILTERS might be a gentler approach.

Re: Ratio Calculation with SUMMARIZE

Anonymous — Thu, 13 Jun 2024 06:42:55 GMT

Hi RedaBi ,

Based on the description, try to modify the dax formula to the following formula.

Ratio = SUMMARIZE( 'Prélevement', 'Prélevement'[Date Opération], 'Prélevement'[Cd Dist], 'Prélevement'[Distrib], 'Prélevement'[Facial], "Total Ventes", SUM('Prélevement'[Facial]), "Total Qte", SUM('Prélevement'[Qte Livree]), "Ratio Vente", SUMX( 'Prélevement', DIVIDE('Prélevement'[Facial], CALCULATE(SUM('Prélevement'[Facial]), ALL('Prélevement'))) ) * 100 )

You can view the following documents to learn more information.

SUMMARIZE function (DAX) - DAX | Microsoft Learn

Row-level security (RLS) guidance in Power BI Desktop - Power BI | Microsoft Learn

Best Regards,

Wisdom Wu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: Ratio Calculation with SUMMARIZE

RedaBi — Thu, 13 Jun 2024 17:18:15 GMT

Thank you for your response ,Unfortunately, it doesn't work. When I drag the "Total Ventes" column along with the 'Prélevement'[Distrib] column into a table, the amount is correct, but the ratio is not. I think the issue lies in calculating the total sales of all distributors with the formula CALCULATE(SUM('Prélevement'[Facial]), ALL('Prélevement')).

Please note that the "Prélevement" table is related to a "Calendar" table. Maybe the total needs to be filtered according to the dates specified in the calendar?

Re: Ratio Calculation with SUMMARIZE

RedaBi — Thu, 13 Jun 2024 17:18:29 GMT

Thank you for your response, Unfortunately, it doesn't work. When I drag the "Total Ventes" column along with the 'Prélevement'[Distrib] column into a table, the amount is correct, but the ratio is not. I think the issue lies in calculating the total sales of all distributors with the formula CALCULATE(SUM('Prélevement'[Facial]), ALL('Prélevement')).

Please note that the "Prélevement" table is related to a "Calendar" table. Maybe the total needs to be filtered according to the dates specified in the calendar?

Re: Ratio Calculation with SUMMARIZE

RedaBi — Fri, 14 Jun 2024 22:00:10 GMT

If it can help, I found a solution to my problem. I used the table only for the sum of sales, linking the table only with the calendar table. Then, I used the sum according to the date filter.