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

    • 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 (
      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

        THANK YOU Jihwan_Kim ! That was exactly the result i needed! appreciate it very much!