Forum Discussion

AlexanderRbt's avatar
AlexanderRbt
Helper I
6 years ago
Solved

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

 

Need Help

5 Replies

  • dax's avatar
    dax
    Community Support
    6 years ago

    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's avatar
      AlexanderRbt
      Helper I
      6 years ago

      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's avatar
    amitchandak
    Super User
    6 years ago

    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's avatar
      AlexanderRbt
      Helper I
      6 years ago

      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.