I would like to create a measure that represents the average of two (or potentially more) categories.
My data looks like this
User Cat_A Value
1 East 30
1 East 60
1 West 50
1 West 100
2 East 10
2 East 20
2 West 60
2 West 70
The desired outcome would be a measure holding for each user the average by category. So, 'user' is a category in itself.
User 1 average East = 45
User 1 average West= 75
User 2 average East = 15
User 2 average West = 65
For one category is successfully used this syntax (produced by PowerBI quick measures):
Average of response average per CAT=
Can anyone help me extend this code from one grouping variable (Cat_A) to two groupings (Cat_A and user)?
Thanks for your help.
I am aware of this post which only groups by one category: https://community.powerbi.com/t5/Desktop/Calculate-Average-per-category/m-p/362637#M163818
Go to Solution.
Average of response average per CAT= CALCULATE(AVERAGE('answers_valid'[value]), ALLEXCEPT('my_data','my_data'[Cat_A],'my_data'[Cat_B]))
Appreciate your KudosConnect with me!
Stay up to date on Read my blogs on
View solution in original post
Please check quick measure option. Right click on a table or field and click on Quick measure. And then try
Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution.Thanks.
Thanks for your idea. This is precisely what I tried with one grouping variable and it works perfectly. However, what if I have two grouping variables (User and Cat_A in my example).
You could try to use below measure or use built-in function like below
Measure 2 = CALCULATE(AVERAGE(t3[value]), ALLEXCEPT(t3,t3[user],t3[cate]))
Best Regards,Zoe Zhi
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thank you @SteveCampbell and @dax for your solutions. You both came to the same conclusion but I can only accept one as the right one.
This works very well can could be extended to groupings of more than three categories.
Kudos to you if you earned one of these! Check your inbox for a notification.
Learn about the award-winning innovation that was implemented across Microsoft’s Business Applications Communities.
Find out where you can attend!