cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
AlexanderRbt Frequent Visitor
Frequent Visitor

Creating average grouped by multiple grouping variables


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

 

1 ACCEPTED SOLUTION

Accepted Solutions
SteveCampbell Established Member
Established Member

Re: Creating average grouped by multiple grouping variables

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  linkedin-logo.png
Read my blogs on  powerbi.tips_.png

View solution in original post

5 REPLIES 5
amitchandak Super Contributor
Super Contributor

Re: Creating average grouped by multiple grouping variables

Please check quick measure option. Right click on a table or field and click on Quick measure. And then try

Screenshot 2019-10-08 01.28.59.png

 

 

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution.
Thanks.

AlexanderRbt Frequent Visitor
Frequent Visitor

Re: Creating average grouped by multiple grouping variables

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 Established Member
Established Member

Re: Creating average grouped by multiple grouping variables

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  linkedin-logo.png
Read my blogs on  powerbi.tips_.png

View solution in original post

Highlighted
Community Support Team
Community Support Team

Re: Creating average grouped by multiple grouping variables

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

401.PNG

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 Frequent Visitor
Frequent Visitor

Re: Creating average grouped by multiple grouping variables

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.

Helpful resources

Announcements
New Kudos Received Badges Coming

New Kudos Received Badges Coming

Kudos to you if you earned one of these! Check your inbox for a notification.

Microsoft Implementation for Communities Wins Award

Microsoft Implementation for Communities Wins Award

Learn about the award-winning innovation that was implemented across Microsoft’s Business Applications Communities.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Kudoed Authors (Last 30 Days)
Users online (1,803)