Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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!
Proud to be a Super User!
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!
Proud to be a Super User!
@Pillz , In case you need column
avergageX(filter(Table,[event category] = earlier([event category])),[attendees])
Write a measure like this
Avg Attendees = AVERAGE ( YourTable[Attendees] )
Then add your [Event Category] and the measure to a matrix visual.
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?
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.
@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
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] )
@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
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!
Proud to be a Super User!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
83 | |
70 | |
60 |
User | Count |
---|---|
150 | |
115 | |
104 | |
89 | |
65 |