Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
skhalatian
Frequent Visitor

powerbi rowlevel filtering and totals

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

4 REPLIES 4
v-jiascu-msft
Employee
Employee

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.

powerbi_rowlevel_filtering_and_totals

 

 

Best Regards,

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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.

powerbi_rowlevel_filtering_and_totals2

 

Best Regards,

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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)

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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