Forum Discussion
Ratio Calculation with SUMMARIZE
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
)
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.
5 Replies
- lbendlin
Super User
Most likely you wanted to use ALLSELECTED rather than ALL.
REMOVEFILTERS might be a gentler approach.
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?
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.
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?
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.
