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 ) )
Jihwan_Kim's avatar
Jihwan_Kim
Super User
4 years ago

Hi, 

I am not sure if I understood your question correctly, but please check the below picture and the measure.

 

 

Row Count Measure: =
COUNTROWS('Table' )
 
  • 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 😞 

    • Jihwan_Kim's avatar
      Jihwan_Kim
      Super User
      4 years ago
      expected result: =
      VAR amounttotal =
      CALCULATE (
      SUM ( 'Table'[Amount] ),
      ALLEXCEPT ( 'Table', 'Table'[Month], 'Table'[Cost Centre] )
      )
      VAR maxperiodnumber =
      CALCULATE (
      MAX ( 'Table'[Pay Period Number ]),
      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 ,

         

        Really appreciate your help. Instead of dividing by max pay period number, i need it to be the count of periods within that month. For example, in your file, 1/08/21 average should be 42/2 (2 because in the month of august, there are 2 pay periods). Similarly, for sept, it should be 45/1 since there is only 1 pay period. 

         

        Hope that makes sense