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 ) )
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!