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!

Pillz's avatar
Pillz
Frequent Visitor
6 years ago

jdbuchanan71 
Here's the model I'm working with


I currently have no measures but in my table right now, the column values are

Event Name, Count of Attendee Name, Event Category 

 



 

jdbuchanan71's avatar
jdbuchanan71
Icon for Super User rankSuper 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
    Icon for Resident Rockstar rankResident 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!

  • Pillz's avatar
    Pillz
    Frequent Visitor
    6 years ago

    jdbuchanan71 

    Seems like Avg Attendees gives me the same result as Attendee Count. I revamped my table so that the each event has a total sum of attendees rather than the count of attendees for that event.

    I think it is because the way my table was setup originally that caused me some confusion