Find Average for another column

[Pillz]

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.

How would I go abouts calculating the average

Thanks 

[richbenmintz]

Hi @Pillz 

I think this Measure will work for you

% By Category = 
var numerator = CALCULATE(SUM('Table'[Attendees]), ALLEXCEPT('Table','Table'[Event Category]))
var denominator =  CALCULATE(COUNTROWS('Table'), ALLEXCEPT('Table','Table'[Event Category]))
 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!

[amitchandak]

@Pillz , In case you need column

avergageX(filter(Table,[event category] = earlier([event category])),[attendees])

[jdbuchanan71]

Write a measure like this

Avg Attendees = AVERAGE ( YourTable[Attendees] )

Then add your [Event Category] and the measure to a matrix visual.

[Pillz]

Thanks for the response,

Actually the attendee column is just a COUNT of Table[Attendee Name] rather than a hard number value. Would that change the approach of my problem?

[jdbuchanan71]

@Pillz 

Please share the layout of your data model so we can understand the tables involved and also any measures you are using and how they are getting calculated.

[Pillz]

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

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

[Pillz]

@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

[richbenmintz · Accepted solution]

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!