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.

How would I go abouts calculating the average

Thanks 

 

 

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!

9 Replies

  • jdbuchanan71's avatar
    jdbuchanan71
    Icon for Super User rankSuper 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
        Icon for Super User rankSuper 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.

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    6 years ago

    Pillz , In case you need column

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

  • richbenmintz's avatar
    richbenmintz
    Icon for Resident Rockstar rankResident Rockstar
    6 years ago

    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!