Creating a Measure (a relativity) that adjusts as you expand levels in a table's row hierarchy?

[Anonymous]

Hi,

I had a question that is directly related with this quick question I asked a couple weeks ago. Please skim through this post first before reading further. Direct link is below, but here's a screenshot of the post: 

https://community.powerbi.com/t5/Desktop/How-to-calculate-a-loss-ratio-relativity-Divide-ratio-for-a...

From that post, I mentioned I created loss ratios from a simple quick measure division:

Ult Loss Ratio = DIVIDE(SUM('Version4'[Ult Losses]), SUM('Version4'[Ult EP])).

I created similar quick measure metrics for 2 other variables (frequency and severity), and used the structure of the formula below (big thanks/credit to @jdbuchanan71 for coming up with this) to create relativities for those as well: 

Relativity = VAR _Overall = CALCULATE ( [Ult Loss Ratio], ALL ( 'Version4' ) )
RETURN DIVIDE ( [Ult Loss Ratio], _Overall )

However, I ran into an issue when I tried displaying all the relativities for the data as I dove levels deeper into my tables' row hierarchies. For a lot of the tables I need to creating, there will be several variables dragged into the Rows field. Year and Zone are just one example of cutting the data. The issue is discussed in after my bolded question in the paragraph below. 

Is it possible to write a measure such that the relativities dynamically adjust as you dive deeper into a table's row hierarchy instead of dividing by the overall totals each time?

As you can see in table 1 below, for the year 2020, the Ult Relativity for the Zone subcategory "Eastern", it divides the 84.9% by the grand loss ratio of 77.0%. However, I want the relativities for these zones to be computed as their loss ratios divided by the 74.1% loss ratio, to see how that zone's loss ratio in 2020 compares to the loss ratio for that year, so 84.9/74.1 = 1.14. 

Table 1:

It would also be great if the Measure could dynamically adjust based on filter selections too, because I'm creating many different tables that cut my data by several diferent variables and don't want to brute force calculate for the 20+ tables I'm creating. So in Table 2 below, which filtered the data to only Central, the grand relativities for would just be 1.00 since I'm only looking at Central data and want the Ult Relativity to divide 75.3/75.3 = 1 (rather than by the grand total of 77% from Table 1), and the relativities in each of these years to be adjusted accordingly too (e.g. 71.1 / 75.3 = 0.94). 

Table 3 below is just another example of how I'm cutting by data by a different variable and would like those same features to be preserved.

Table 2:

Table 3:

I would greatly appreciate any help on this! Thanks!

[jdbuchanan71]

@Anonymous 

We should be able to adjust the measure like so to using ALLSELECTED instead of ALL.  Not sure if the region is the only level where you want to use the year level rather than the total but I added both region and years in business to the check.  If you are looking at that level it will use the Ult Loss Ratio from the years that apply to the row of the matrix.  If you don't have a filter on year it will look at all years.

Relativity = 
VAR _Overall = CALCULATE ( [Ult Loss Ratio], ALLSELECTED ( 'Version4' ) )
VAR _OverallYr = CALCULATE ( [Ult Loss Ratio], ALLSELECTED ( 'Version4' ), VALUES(Version4[Year] ) )

RETURN 
    DIVIDE ( [Ult Loss Ratio], 
        IF ( ISINSCOPE ( Version4[Region] ) || ISINSCOPE ( Version4[Years in Business] ), _OverallYr, _Overall)
)

I have attached my sample file for you to look at incase it will help.

[Anonymous]

@jdbuchanan71 Thank you for this and I just had some follow up questions. 

Would I still be able to preserve these features (use cases are the questions listed below) all under that same Relativity measure? 

What if I had several different parent row levels besides Year that I wanted to look at? I'll be creating many pages, each of which has a table with all those loss metrics but with a different parent row level variable (e.g. Business Type) and different subrow levels?

What if I needed to filter across all pages by a different variable(s)? 

What if I had several sublevels dragged in (e.g. in this page you could dive a level deeper and expand Region to State)?

[v-deddai1-msft]

Hi @Anonymous ,

You need to modify your Relativity measure according to the actual row hierarchy. You can use the ISINSCOPE function to get the row hierarchy you want. Please refer to :https://www.kasperonbi.com/use-isinscope-to-get-the-right-hierarchy-level-in-dax/

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Best Regards,

Dedmon Dai

[Anonymous]

Hi @v-deddai1-msft 

Just read the article, thanks for this. This would still be dependent on which row variables I bring into my matrix right? So I would have to create a new Relativity measure for every different matrix visual I create because I won't be bringing in the same row variables each time

[v-deddai1-msft · Accepted solution]

Hi @Anonymous ,

Yes, you are right. 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Best Regards,

Dedmon Dai

[jdbuchanan71]

@Anonymous 

The measure I gave you no, it is focused around year as the parent level.  I think what you want is doable but complex as you have to determine how to deal with all the levels combined with all the ways you want to organize the data.  Take a look at this article that talks about working with hierachies in DAX.

https://www.sqlbi.com/articles/clever-hierarchy-handling-in-dax/