Setting up row security level - Company target and individual target

[basyan1991]

Hi 

I am working on a PowerBI report to sum up the sales of different team. And compare with this month's target.

I have a spreadsheet with individual team sales and target. i use a measure to sum up all the teams number to become a company target and sales number.

From the admin view, I will see both individual team numbers and company number.

But when I set the security level under the team sales & target spreadsheet.

(i.e team a manager will be able to see his team sales and whole company sales)

It will affect the company target and sales number. it makes both company target and team target having the same number. 

Is there a way to solve it?

Thanks a bunch! 

[PC2790]

From what I could perceive from your issue is that you are looking to ignore the filter to get the company's target value while applying RLS on the team level.

You must be using a measure to get the sum, you can use ALL function for that like- SUMX(Sum(sales), ALL(Team))

Hope this solves your issue.

[basyan1991]

Hi 

sum(Forecast[Monthtarget]), ALL(Forecast[Team]), REMOVEFILTERS(Forecast[Team])

this is what i am having now. 
but the issue is the table [team] consists team A,B,C

when i set the security level to allow manager A to view Team A number
the report wont be able to show Team B & C number which makes the calculation of total company number wrong

[PC2790 · Accepted solution]

Ok..Got it!

Can you try this?

[basyan1991]

Thanks for sharing this. My problem is solved now..

Thank you for your help!

[amitchandak]

@basyan1991 , You need RLS with organization Hierarchy

https://radacad.com/dynamic-row-level-security-with-organizational-hierarchy-power-bi

[basyan1991]

Thanks for sharing that. 
But not sure whether setting this up will help to solve my issue.

I am able to set up the report with records related to the manager. But fail to extract the company total number. For your example, other that the sales number 610 of Reza's team. I need to display the number of the total sales 1065 as well.
But by filtering out other team's sales. I failed to get that 1065 number.