Aggregated Measures with RLS

[MrCoyado]

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.

[kaisrec]

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]

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 · Accepted solution]

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