Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi,
I am building a report that have multiple regions, and I want to use powerbi rowlevel filtering so certain users in each region will see the information about that region.
Everything works as expected. The issue I’m having is that I have a pie chart what shows the percentage of sales for that region against the total sales.
Since I am using rowlevel filtering the results sets for the other regions get filtered out and therefore the total sales will not be correct and the pie chat will be wrong
What is the best way to achieve the region segregation without creating multiple reports
Thanks
Hi @skhalatian,
There could be a workaround. If people still have the permission to see the sales of other regions, you can create a calculated table that won't be affected by the RLS. If not, that would be complicated. You still can create many calculated table for each salesman.
Best Regards,
Dale
perfect that is a step in the right direction
however for the pie chart I only want to see the selected region vs total.
so basiclly any region I choose through the row level filter, I want to see that region vs total for all regions in pie chart
hope that makes sense
Hi @skhalatian,
There is a solution. This idea is from Slicer-changing-total. Please check out the demo in the attachment. The steps below follow the steps I posted before.
1. Create a measure for the item filtered by RLS.
Selected = CALCULATE ( SUM ( 'SummarizeTable'[sales] ), FILTER ( ALL ( 'SummarizeTable'[ColorName] ), 'SummarizeTable'[ColorName] IN VALUES ( DimProduct[ColorName] ) ) )
2. Create a measure to calculate the value left.
Others = CALCULATE ( SUM ( 'SummarizeTable'[sales] ), FILTER ( ALL ( 'SummarizeTable'[ColorName] ), NOT 'SummarizeTable'[ColorName] IN VALUES ( DimProduct[ColorName] ) ) )
3. Create a pie chart.
Best Regards,
Dale
That's very clever way of getting around the problem
going back to your solution if the row level filter is combination of the colors
[ColorName] = "Black" || [ColorName] = "BLUE"
the selected will aggrigate the regult for both colors. I want to see them sepratly in the pie chat so for this eacple the pie chart will have two slices black and blue vs total . ( not summed up)
User | Count |
---|---|
128 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
136 | |
113 | |
102 | |
71 | |
60 |