Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Return max row count

Hi All,    I have a table like the following:    Month Cost Centre Pay Period Number  1/7/21 XX1 1 1/7/21 XX1 2 1/7/21 XX1 3 1/8/21  XX1 4 1/8/21  XX1 5 1/...
Need Help
Show and Tell
Tips and Tricks
  • Jihwan_Kim's avatar
    Jihwan_Kim
    4 years ago

     

    expected result: =
    VAR amounttotal =
    CALCULATE (
    SUM ( 'Table'[Amount] ),
    ALLEXCEPT ( 'Table', 'Table'[Month], 'Table'[Cost Centre] )
    )
    VAR maxperiodnumber =
    CALCULATE (
    COUNTROWS('Table'),
    ALLEXCEPT ( 'Table', 'Table'[Month], 'Table'[Cost Centre] )
    )
    RETURN
    IF ( HASONEVALUE ( 'Table'[Month] ), DIVIDE ( amounttotal, maxperiodnumber ) )
Anonymous's avatar
Anonymous
Not applicable
4 years ago

Hi Jihwan_Kim ,

 

Thanks for that. I basically need to sum the values for any given month and then based on the row count, i need to divide the value to get an average. 

 

Result will be displayed in a card.

 

For example, for the below, i would sum up amount (5 + 34 + 2 = 41) and then 41/max no. of pay periods which in this case is 3. 

 

This will be displayed in a card visual and it should work dyniamcally with the dimdate slicer

MonthCost CentrePeriod NumberAmount
1/7/21 15
1/7/21 234
1/7/21 32
AllisonKennedy's avatar
AllisonKennedy
Community Champion
4 years ago

Anonymous You can use AVERAGEX for this:

 

AVERAGEX(VALUES(DimDate[Month]), [Measure])

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi AllisonKennedy ,

     

    Thanks for that! I did try that but it's not giving me the right output. 


    Essentially, within a month, we have varying numbers of pay periods. So for Sept, we have 3 whereas in August, we only had 2. So we capture data each pay period but i need to essentially sum up values for that month and then determine how many pay periods there were and divide by that to average it.

     

    When i use the DAX above, i'm not getting the average for some reason 😞 

    • AllisonKennedy's avatar
      AllisonKennedy
      Community Champion
      4 years ago

      Anonymous  Glad you solved it - here's a post on Averages that you may find helpful??? https://excelwithallison.blogspot.com/2020/09/what-does-average-mean.html

       

      Basically, if you want to average over number of pay periods, then that needs to be the first argument in your AVERAGEX function:

       

      Measure = AVERAGEX( Values( Table[PayPeriod] ) , [Measure to Average] )

       

      Hope that helps you in future!