Ratio Calculation with SUMMARIZE

[RedaBi]

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
)

 

[RedaBi · Accepted solution]

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.

[Anonymous]

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.

[RedaBi]

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?

[lbendlin]

Most likely you wanted to use ALLSELECTED rather than ALL.

REMOVEFILTERS might be a gentler approach.

[RedaBi]

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?