See total sales despite rolle filter

[Jan_Trummel]

Hello to the forum,

I have the sales of people A, B, C, D and E. Here is the Sales table:

Tables "Sales"

I want to make sure each person only sees their data. For example, I create the following role for person A:

[person] == "A"

The challenge now is that I also want to display the total sales in the report. However, because the data is filtered by the role, only that person's data is visible (in the example, only Person A's $100). One question is therefore how I can still access the total sales using a measure.

So my goal looks like this (with role filter active for person A):

This would be my goal for Person A

I've already tried it with the following measures:

Total sales = CALCULATE(SUM(Sales[Amount]), REMOVEFILTERS(Sales[Person]))

Total sales = CALCULATE(SUM(Sales[Amount]), ALL(Sales[Person]))

However, in both cases, Total sales only reports Person A's sales.

Is it even possible to “break through” the role filter with DAX?

The only other option I can think of would be to duplicate the table. However, this could quickly lead to performance problems with large amounts of data and would also make the data model very confusing. Therefore, it would be great if I could calculate the total sales using a measure from the original table.

[Jihwan_Kim · Accepted solution]

Hi,

I am not sure how your semantic model looks like, but in my opinion, if RLS is implemented, the total might not be shown as you want. I suggest having one more fact table, not dupulicating only, but also making it aggregated. And then if the row on the report is filtered by sales person, show sales person's amount, otherwise show agg table's amount.