Forum Discussion

Pelle's avatar
Pelle
Frequent Visitor
8 years ago
Solved

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

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: ...
Need Help
  • v-piga-msft's avatar
    v-piga-msft
    8 years ago

    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

v-piga-msft's avatar
v-piga-msft
Icon for Resident Rockstar rankResident Rockstar
8 years ago

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's avatar
Pelle
Frequent Visitor
8 years ago

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