Creating average grouped by multiple grouping variables

[AlexanderRbt]

Hi all,

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

3 ...

The desired outcome would be a measure holding for each user the average by category. So, 'user' is a category in itself.

Example outcome:

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= 
AVERAGEX(
	KEEPFILTERS(VALUES('my_data'[Cat_A]) ),
	CALCULATE(AVERAGE('answers_valid'[value]))
)

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

[dax]

Hi AlexanderRbt,

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]))

or

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.

[AlexanderRbt]

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.

Thanks!

A.

[amitchandak]

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.

[AlexanderRbt]

Hello @amitchandak 

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).

Any ideas?

Thanks,

A.

[SteveCampbell · Accepted solution]

Try:

Average of response average per CAT= 
  CALCULATE(AVERAGE('answers_valid'[value]),
    ALLEXCEPT('my_data','my_data'[Cat_A],'my_data'[Cat_B]))

Appreciate your Kudos
Connect with me!

Stay up to date on  
Read my blogs on