RLS with multi-tenant data and overall aggregates

[horizondave]

Is this possible with Power BI?

If I understand RLS correctly, all of my data has a tenant-id which I can use to filter by customer....So I can give customer 1 their data, customer 2 theirs but Nobody linked to Customer 1 can see data for Customer 2.

However, I would like the customers to be able to compare their aggregates against "overall" aggregates obtained from all data from all customers.

Something like this:

Customer 1 sees:

    No. of widgets this month: 20

    Overall Average widgets this month: 16.8

    Performance this month: +3.2

Customer 2 sees:

   No of widgets this month: 15

   Overall Average widgets this month: 16.8

   Performance this month: -1.8

ie, all customers share the average number of widgets, but they only see their own widget totals

[v-jiascu-msft]

Hi @horizondave,

The solution could be that you can add the Overall as a calculated column. Or create a summarized table.  You can try it out. 

Best Regards!

Dale

[horizondave]

The trouble is I potentially have *lots* of these attributes I want to add as averages....It's a very time consuming option to have to create each one individually.

[v-jiascu-msft · Accepted solution]

Hi @horizondave,

I'm afraid this is restricted by the way how RLS works. Maybe you can add many attributes in one calculated table like this:

Table =
ADDCOLUMNS (
    { "Summarized Values" },
    "Total Sales Quantity", SUM ( 'Sales'[Quantity] ),
    "Total Sales Amount", SUMX ( Sales, Sales[Net Price] * Sales[Quantity] ),
    "Number of kinds of Products", DISTINCTCOUNT ( 'Product'[Brand] )
)

Best Regards!

Dale

[v-jiascu-msft]

Hi @horizondave,

Could you please mark a answer for this thread?

Best Regards,

Dale

[horizondave]

I have accepted your answer as a potential solution, but my actual solution was to not use Power BI as it wasn't a good fit for my solution for other reasons.