How can I do this with a single DAX expression

DAX DAX Calculations
1

I have some hierarchical data: Simulation–>Year—>Site–>System–>Asset.
I created measures FCI and FCIAve that work correct at whatever context the report is set up with, based on the Assets within the context.

The most important FCI value is calculated at the Site level. I desire to calculate the Average yearly FCI across all Sites at the Site level and report it back regardless of the context of the report. The good news is that I have done it. But I’m not happy with the way I did it because I had to create a Table and a Lookup Measure to accomplish my goal. I’ve been trying make this work with a single DAX expression, but I need some fresh thinking.

How can I achieve the same results with a single DAX expression?
Here’s my starting point:

image
image.png967×647 18.2 KB

Here’s the intermediate table with the desired values calculated:

image
image.png854×225 12.7 KB

Here’s the Lookup expression I created:

image
image.png882×147 7.28 KB

And here are some visuals showing that it all works as expected:

image
image.png938×490 38.1 KB

Any idea how can I accomplish the same with one DAX expression?

3

Thanks for all the detail.

Will see if I can work through this.

Definitely is sounds like you want to be iterating through something so AVERAGEX is likely the function required here.

I see you already have this in the calculated table.

If you want to calculate the average for an entire year regardless of the date context you need something like this.

AVERAGEX(
      CALCULATETABLE( ALL( Dates ), VALUES( YearColumn ),
                 FCI measure )

This should work if the initial context is sites column.

ALL removes any context coming from Date table then VALUES puts it back on only at the yearly level.

See if this works.

Sam