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

Aggregated Measures with RLS

Hello everyone.

 

We have just implemented Power BI as our BI solution in my company (retail business).

We are using RLS in our sales reports, which respect the company's hierarchy levels:

 

Sales Director --> Regional Sales Manager --> Regional Sales Rep --> Retail Shop

 

Thus, when a regional sales rep access the report, he can only see the info concerned to his group.

However, our sales director has demanded that both the regional sales rep and the regional manager be able to compare his results to the results of the level above his. See the example below:

 

Regional Sales Rep overall sales result: +4%

Regional Sales Manager overall sales result: -3%

Sales Director overall sales result: +9%

 

In this case, what our director wants is that the regional sales rep be able to see that he is performing better than his region (without seeing the result of the other individual sales reps), but below the company's overall result. The same for the regional manager. He should be able to compare his region with the overall company's result in an aggregated manner (so he can't see the individual results of other regions).

 

Is this possible with Power BI? We've tried using functions such as ALL and ALLSELECTED, but it didn't work with RLS.

 

Since we are considering using Power BI as the main BI solution for our retail business, this functionality is crucial to our decision whether we stay with Power BI or move on to another solution!

 

Thanks.

1 ACCEPTED SOLUTION
v-shex-msft
Community Support
Community Support

Hi @MrCoyado,

 

>>Is this possible with Power BI? We've tried using functions such as ALL and ALLSELECTED, but it didn't work with RLS.

 

Based on test, I find that RLS is filtered on the dataset(original data will be filtered), so all and allselected function not work on RLS.

 

In my opinion, you can calculate original data at query editor or database side and summary these records to a table. Then use the summary table to compare with current data.

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

View solution in original post

3 REPLIES 3
kaisrec
Advocate I
Advocate I

While RLS filters data at the row level, you can create measures that aggregate results at different hierarchy levels for comparison.

Here's a general approach you can follow:

  1. Create Aggregated Measures: Define measures that aggregate results at different hierarchy levels. For example:

    • Regional Sales Rep Sales: SalesRepSales = SUM('Sales'[SalesAmount])
    • Regional Manager Sales: ManagerSales = CALCULATE(SUM('Sales'[SalesAmount]), ALL('SalesRep'))
    • Director Sales: DirectorSales = CALCULATE(SUM('Sales'[SalesAmount]), ALL('RegionalManager'))

    These measures will disregard the RLS filtering and provide aggregated sales amounts.

  2. Create Comparison Measures: Calculate the percentage difference between the current level and the level above. For example:

    • Rep vs. Manager: RepVsManager = ([ManagerSales] - [SalesRepSales]) / [SalesRepSales]
    • Manager vs. Director: ManagerVsDirector = ([DirectorSales] - [ManagerSales]) / [ManagerSales]
  3. Implement RLS for Individual Level Security: Continue using RLS for individual-level security, ensuring that users at each level can only see their own data.

  4. Use Measures in Reports: In your Power BI reports, use the newly created measures for comparisons. The measures take into account the hierarchy levels and allow for comparing results without exposing individual results.

The use of ALL and ALLSELECTED functions is essential for creating measures that operate outside the filter context imposed by RLS.

vmsouza30
Helper I
Helper I

How do you  calculate original data at query editor or database side and summary these records to a table. Then use the summary table to compare with current data.

 

Can you Help me, I jave the same problem!

v-shex-msft
Community Support
Community Support

Hi @MrCoyado,

 

>>Is this possible with Power BI? We've tried using functions such as ALL and ALLSELECTED, but it didn't work with RLS.

 

Based on test, I find that RLS is filtered on the dataset(original data will be filtered), so all and allselected function not work on RLS.

 

In my opinion, you can calculate original data at query editor or database side and summary these records to a table. Then use the summary table to compare with current data.

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

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.

Top Solution Authors