cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
skhalatian Regular Visitor
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
v-jiascu-msft Super Contributor
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.

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

v-jiascu-msft Super Contributor
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.

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

 

Helpful resources

Announcements
Back to School Contest

Back to School Contest

Engage and empower students with Power BI!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Summit Australia 2019

Summit Australia 2019

Travel to Melbourne and network with thousands of peers!

Top Ideas
Users Online
Currently online: 110 members 1,520 guests
Please welcome our newest community members: