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:
Here’s the intermediate table with the desired values calculated:
Here’s the Lookup expression I created:
And here are some visuals showing that it all works as expected:
Any idea how can I accomplish the same with one DAX expression?