Forum Discussion

RedaBi's avatar
RedaBi
Frequent Visitor
2 years ago
Solved

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 ...
  • RedaBi's avatar
    RedaBi
    2 years ago

    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's avatar
Anonymous
Not applicable
2 years ago

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's avatar
RedaBi
Frequent Visitor
2 years ago

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?