Manage Security Roles - Row Level Security summary figures

[CPDav]

Hi all, 

My dashboard shows sales and call figures for by postcode, territory, region and national levels. 

I have assigned Sales reps who can only view sales and calls by their territory, whilsts Regional Sales reps can view sales and calls by their region and the split by territory whilst a select group can view all sales and calls by each region and territory.

Is there a way where the Sales rep can still only view their territory but can see the total for their region and the total national figure, and the same for Regional sales reps, can they view their full region breakdown but also the national roll up?

The purpose is so that each viewer can see how they are comparing to 1. National Figures and 2. Their Region.

Thank you in advance!!

[johnbasha33]

@CPDav 

Yes, you can achieve this level of access control and data visibility in Power BI using Row-Level Security (RLS) and hierarchical filtering. Here's a general approach you can take:

1. **Create Roles:** Define roles for Sales reps, Regional Sales reps, and the select group with different levels of access. You can do this by going to the Modeling tab in Power BI Desktop, selecting "Manage Roles," and creating roles based on your criteria (e.g., Sales Rep, Regional Sales Rep, National Sales Rep).

2. **Implement Row-Level Security (RLS):** Use RLS to restrict data access based on the roles you've defined. For example:
- For Sales reps, filter the data to only show their assigned territory.
- For Regional Sales reps, filter the data to show their region and the territories within that region.
- For the select group with access to all regions, no filtering is applied.

3. **Hierarchical Filtering:** Set up hierarchical filtering in your visuals to allow users to drill down from territory to region to national level. This way, Sales reps can see their territory's data while also viewing the total for their region and the national figure.

4. **Create Calculated Columns or Measures:** You may need to create calculated columns or measures to calculate totals at different levels of hierarchy (e.g., region total, national total).

5. **Test and Validate:** Ensure that the RLS rules are correctly applied by testing with different user roles. Verify that Sales reps can only see their territory's data, Regional Sales reps can see their region's data and the national total, and the select group can see data at all levels.

By following these steps, you can provide Sales reps and Regional Sales reps with access to their respective data while allowing them to compare their performance to regional and national figures. It's a powerful way to tailor access to the specific needs of different user groups while maintaining data security and integrity.

Did I answer your question? Mark my post as a solution! Appreciate your Kudos !!