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:
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

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

2 Replies

  • v-piga-msft's avatar
    v-piga-msft
    Resident 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