Hierarchical calculation of averages

[jd_87]

I'm working with some hierarchical data and am running into issues in my tables when calculating the average values at higher levels, since the higher level seems to only average the values of lower levels rather than calculating it separately (i.e., it doesn't account for differences in denominators between groups). I think I've seen similar questions (e.g., here) - but that example was from many years ago and I'm not sure if there are solutions now. As an example:

I have two tables:

• Table 1: describes the hierarchical relationship (in this example, 2 levels each with unique sublevels)

• Table 2 contains the data for each of the sublevels:

The tables are connected in a 1-to-many relationship (Table 1 [1] : Table 2 [*]).

When I look at all the full data in a table in the report view, the results are what I want (i.e., the Value column = Numerator / Denominator):

BUT, when I want to look at the data aggregated by Level 1, the Values are not what I want (i.e., the Value column does NOT equal Numerator/Denominator; the Values should be 0.095 and 0.012):

This is a simplistic example, but the issue arises in a few different contexts. Are there DAX formulae to help with this? Or other workarounds that people use?

[Greg_Deckler · Accepted solution]

@jd_87 For your example, you would create a measure like this:

Measure = DIVIDE( SUM( 'Table'[Numerator] ), SUM( 'Table'[Denominator] ) )

For more explicit control over your values at different hierarchy levels, refer to this: Matrix Measure Total Triple Threat Rock & Roll - Microsoft Fabric Community

[jd_87]

Thanks @Greg_Deckler , that does answer the question I posed, but I'm now realizing it doesn't solve the actual crux of my problem, since my problem is a bit more complex. Not sure if you have the time to think about this, but:

We have an additional column, Metrics, and for each of our lowest categories (i.e., Level 2) we're collecting a range of different metrics (call them "XX", "YY", "ZZ" - so Level 2 "A" will have three rows corresponding to each of those three metrics, "B" will have the same and so on). My desired outcome is to have a slicer where the user can select the metric their interested in, and the table will update accordingly. I believe with that DAX formula you gave, it averages the result for each of the three metrics for each Level 2. So I guess I would need a way to specify - perform that calculation only on the selected metric, and leave out the others. Does that make sense?

(Also, that link you provided will be very helpful, as our hierarchy is much bigger than the example I gave)

[Greg_Deckler]

@jd_87 If you can provide a sample file or better sample data, happy to take a look. I like to solve these kinds of problems.

[jd_87]

Thanks @Greg_Deckler - upon closer examination, it appears the fault is entirely mine! Your solution worked with extra sample data I created, which then prompted me to look closer at my data model and I found some issues there. So I think I'm good to go - thanks for your help 🙂