Forum Discussion

SidharthK16's avatar
SidharthK16
Frequent Visitor
3 years ago
Solved

Column and Row level average

Hi All, Hope you are doing great! I need some help in Dax Query. where I need to calculate Row level and Column level average. Sharing below the excel for the reference. Row Labels ...
  • amitchandak's avatar
    amitchandak
    3 years ago

    SidharthK16 , Assume you already have % measure, Try a measure like

     

    Switch(True(),
    not(isninscope(Table[Drink])) && isninscope(Table[Category]), AverageX(Values(Table[Drink]), [Measure]),
    not(isninscope(Table[Category])) && isninscope(Table[Drink]), AverageX(Values(Table[Category]), [Measure]),
    inot(isninscope(Table[Category])) && not(isninscope(Table[Drink])) , AverageX(Summarize(Table, Table[Drink],Table[Category]), [Measure]),
    [Measure])

     

     

    https://www.kasperonbi.com/use-isinscope-to-get-the-right-hierarchy-level-in-dax/

amitchandak's avatar
amitchandak
Super User
3 years ago

SidharthK16 , Assume you already have % measure, Try a measure like

 

Switch(True(),
not(isninscope(Table[Drink])) && isninscope(Table[Category]), AverageX(Values(Table[Drink]), [Measure]),
not(isninscope(Table[Category])) && isninscope(Table[Drink]), AverageX(Values(Table[Category]), [Measure]),
inot(isninscope(Table[Category])) && not(isninscope(Table[Drink])) , AverageX(Summarize(Table, Table[Drink],Table[Category]), [Measure]),
[Measure])

 

 

https://www.kasperonbi.com/use-isinscope-to-get-the-right-hierarchy-level-in-dax/

SidharthK16's avatar
SidharthK16
Frequent Visitor
3 years ago

Thank you for help
I have to do some changes and its working

Thanks again