Create a calculation with two different levels in a dimension table.

[Pelle]

Hi.
I need to do a calculation like this.
Avg(value) for level1 DIVIDED Avg(value) for level2.
Her is my data.
dimension table:

fact table:

I have created two different measurs:
Avg Value = AVERAGE('Fact'[Value])
Avg Value without Level1 = CALCULATE(AVERAGE('Fact'[Value]);ALL(Dim[Level1]))
The result in a table:

But if I remove the Level2 from the table it ends up dowing it wrong.

I understand why but how do I make a measure that is not depended on the visual row context?
Thanks / P

[v-piga-msft · Accepted solution]

Hi @Pelle,

You could try to modify your measure as below.

Avg Value without Level1 =
CALCULATE (
    AVERAGE ( 'fact'[Value] ),
    FILTER ( ALL ( dim ), 'dim'[Level2] = MAX ( 'dim'[Level2] ) )
)

And the result will be your expected output.

Hope it can help you!

Best Regards,

Cherry

[Pelle]

This solution works great in powerbi dax but I'm using SSAS tabulare 2016 in DirectQuery mode.
The Max() function only works with numbers or dates (except when used in inmemory 🙂 ).

So I'm stuck with my old code but it turned out alright as long as I have Level2 attributes with me in my visuals.

Or is there another work around?

/P