cancel
Showing results for
Did you mean:
Regular 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
Super Contributor

## Re: powerbi rowlevel filtering and totals

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

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.
Regular Visitor

## Re: powerbi rowlevel filtering and totals

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

Super Contributor

## Re: powerbi rowlevel filtering and totals

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

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.
Regular Visitor

## Re: powerbi rowlevel filtering and totals

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)

Announcements

#### Back to School Contest

Engage and empower students with Power BI!

#### Watch Sessions On Demand!

Continue your learning in our online communities.

#### Summit Australia 2019

Travel to Melbourne and network with thousands of peers!

Top Ideas
Top Kudoed Authors
Users Online
Currently online: 110 members 1,520 guests
Recent signins: