Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
Solved! Go to Solution.
@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 , 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 working
Thanks again
User | Count |
---|---|
57 | |
21 | |
18 | |
16 | |
12 |
User | Count |
---|---|
85 | |
54 | |
39 | |
21 | |
18 |