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

Write a measure like this

Avg Attendees = AVERAGE ( YourTable[Attendees] )

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

Pillz's avatar
Pillz
Frequent Visitor
6 years ago

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

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