Using RLS with DAX SUM

[ctoscher]

We have a table with a measure that divides the Balance value of that row by the sum of the Balance column in that table (Concentration risk). This is what we get:

Client	Account	Balance	Concentration
ABC	123456	10	0.1
ABC	123456	20	0.2
ABC	987654	20	0.2
XYZ	234567	40	0.4
XYZ	234567	10	0.1

Using RLS (Modeling > View As) and specifying a client (ABC), we'd expect to get the following table:

Client	Balance	Concentration
ABC	10	0.2
ABC	20	0.4
ABC	20	0.4

We instead get the following:

Client	Balance	Concentration
ABC	10	0.1
ABC	20	0.2
ABC	20	0.2

This obviously doesn't make sense for our clients because their concentration would never be 100%.

It would also create an issue for us because now they can see how much their own concentration risk on our portfolio is (in the case above, 50%).

Our Concentration column is a measure set to the following:

Concentration = MyTable[Balance]/SUM(MyTable[Balance])

How can we limit the SUM to be specific to the user (client) viewing the report?

Separate to this, we have a filter that spans all reports for subaccounts, how can filter the values to show only relevant rows (Account in first table)?

[edhans · Accepted solution]

You need to calculate the concentration on the fly as a measure, not rely on a balance row. The balance field won't recalculate based on RLS. Whatever is in a field is permenant until the next refresh.
It would need to be simlar to this:

Concentration = 
VAR varNumerator =
    SUM( 'Table'[Balance] )
VAR varDenominator =
    CALCULATE(
        SUM( 'Table'[Balance] ),
        REMOVEFILTERS( 'Table' )
    )
VAR Result =
    DIVIDE(
        varNumerator,
        varDenominator,
        0
    )
RETURN
    Result

[ctoscher]

Wow this is fantastic, we're new to Power BI and still learning DAX. This'll be a powerful tool for us to use once we learn more!

[edhans]

Glad to help @ctoscher - It is an incredibly powerful tool. Learning DAX can be amazing and tricky at the same time. Post back with any questions to the forums.