Forum Discussion
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 | Sprite | Mazza | Limca | Average |
| A | 14.0% | 25.3% | 15.9% | 18.4% |
| B | 10.1% | 9.6% | 9.0% | 9.6% |
| C | 8.0% | 10.2% | 6.7% | 8.3% |
| D | 13.1% | 9.7% | 10.3% | |
| E | 20.8% | 39.2% | 22.7% | 27.6% |
| Average | 13.2% | 19.5% | 12.8% | 15.3% |
So, as shown below we have 5 Row level and 3 Brands in column.
What I need is to calculate Column and Row average(Highlighted in Bold).
Please give your expert advice.
Thanks
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/
2 Replies
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/
Thank you for help
I have to do some changes and its workingThanks again
