Forum Discussion

jcastr02's avatar
jcastr02
Post Prodigy
5 years ago
Solved

average by month

Hello I'd like to have an average summarized by months for "headcount"   When I click average  - it's averaging the individual numbers vs. summarizing by months.....See example below.....(looking at headcount column)   

 

 

2937
3101
3151
3270
3359
3536
3843
3974
3692
3938
Average should be 34,801 / 10 = 3,480

 

Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    Hi jcastr02 

    If you have only one date for per month per year, this measure may works corretly. Ex: Only 2019/10/1 for 2019 Oct.

    In your screenshot you seem to build a table visual by Measure without add CountType. It may only show Term's average to you.(T>H and you use Max function)

    You can add CountType column in table visual and try again. Or you can try to build a Matrix visual and add CountType in Columns and Measure in Values.

    Only Measure:

    CountType and Measure:

    If you only want to see the Headcount average you can update your Measure as below.

     

    Avg of Headcount = 
var _Sum = SUMX(FILTER('Table','Table'[CountType]="Headcount"),'Table'[Count])
var _Month = CALCULATE(DISTINCTCOUNT('Table'[Date]),FILTER('Table','Table'[CountType]="Headcount"))
return
DIVIDE(_Sum,_Month)

     

    Result:

    If this reply still couldn't help you solve your problem, could you share your pbix file with me by your Onedrive for Business?

    And this will make it easier for me to understand your data model and your require.

    You can download the pbix file from this link: average per month

     

    Best Regards,

    Rico Zhou

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. 

     

9 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    jcastr02 , Try like

     

    AverageX(values(Date[Month year]), sum(Table[headcount]))

    AverageX(summarize(Table, Date[Month year], "_1", sum(Table[headcount])),[_1])

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    5 years ago

    jcastr02 This looks like a measure aggregation problem. See my blog article about that here: https://community.powerbi.com/t5/Community-Blog/Design-Pattern-Groups-and-Super-Groups/ba-p/138149

    The pattern is:
    MinScoreMeasure = MINX ( SUMMARIZE ( Table, Table[Group] , "Measure",[YourMeasure] ), [Measure])
    MaxScoreMeasure = MAXX ( SUMMARIZE ( Table, Table[Group] , "Measure",[YourMeasure] ), [Measure])
    AvgScoreMeasure = AVERAGEX ( SUMMARIZE ( Table, Table[Group] , "Measure",[YourMeasure] ), [Measure])
    etc.

    • jcastr02's avatar
      jcastr02
      Post Prodigy
      5 years ago

      amitchandak Thanks so much...

       

      "headcount" is not a column in my query...what I can add below so it's only using the values from the 'count type" - headcount?

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        5 years ago

        Hi jcastr02 

        I build a sample table like yours to have a test.

        Measure:

        Measure = 
AVERAGEX(FILTER(ALL('Table'),'Table'[Date]=MAX('Table'[Date])&&'Table'[CountType]=MAX('Table'[CountType])),'Table'[Count])

        Result:

        If you have different days in the same month, you can add two calculated columns.

        Year = Year('Table'[Date])
        Month = Month('Table'[Date])

        Change Measure:

        Measure = 
AVERAGEX(FILTER(ALL('Table'),'Table'[Year]=MAX('Table'[Year])&&'Table'[Month]=MAX('Table'[Month])&&'Table'[CountType]=MAX('Table'[CountType])),'Table'[Count])

        You can download the pbix file from this link: average by month

         

        Best Regards,

        Rico Zhou

         

        If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.