cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

## 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
)``````

1 ACCEPTED SOLUTION
Frequent Visitor

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 5
Frequent Visitor

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.

Community Support

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
)
``````

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.

Frequent Visitor

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?

Super User

Most likely you wanted to use ALLSELECTED rather than ALL.

REMOVEFILTERS might be a gentler approach.

Frequent Visitor

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?