Forum Discussion

Pillz's avatar
Pillz
Frequent Visitor
6 years ago
Solved

Find Average for another column

Hi, I'm trying to find the average of event attendees for each category. Example: average for Category 1 would be 19.5 (15+24/2) Attached is an example of the table data that I have right now. ...
Need Help
  • richbenmintz's avatar
    richbenmintz
    6 years ago

    Hi Pillz,

     

    Given your Data Model I think the following will work

     

    % By Category = 
var numerator = CALCULATE(COUNTROWS('Data'), ALLEXCEPT('Data','Data'[Category]))
var denominator =  CALCULATE(COUNTROWS(SUMMARIZE(ALLEXCEPT(Data, 'Table'[Event Category]), Data[Event], "m", COUNTROWS(Data))))
 return 
divide(numerator,denominator)

    Hope this Helps

    Richard 


    Did I answer your question? Mark my post as a solution!
    Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

jdbuchanan71's avatar
jdbuchanan71
Super User
6 years ago

Thank you.

First, make your count into a measure:

Attendee Count = DISTINCTCOUNT ( Data[Attendee Name] )

Then you can write the average measure like so.

Avg Attendees = AVERAGEX ( VALUES ( Data[Category]), [Attendee Count] )

 

richbenmintz's avatar
richbenmintz
Resident Rockstar
6 years ago

Hi Pillz,

 

Given your Data Model I think the following will work

 

% By Category = 
var numerator = CALCULATE(COUNTROWS('Data'), ALLEXCEPT('Data','Data'[Category]))
var denominator =  CALCULATE(COUNTROWS(SUMMARIZE(ALLEXCEPT(Data, 'Table'[Event Category]), Data[Event], "m", COUNTROWS(Data))))
 return 
divide(numerator,denominator)

Hope this Helps

Richard 


Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!