Skip to main content
cancel
Showing results for 
Search instead 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

Reply
RedaBi
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
)

 image.pngimage.png

1 ACCEPTED SOLUTION
RedaBi
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.

View solution in original post

5 REPLIES 5
RedaBi
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.

v-jiewu-msft
Community Support
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
)

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?

lbendlin
Super User
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?

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors