Forum Discussion
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/...
- 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] ))RETURNIF ( HASONEVALUE ( 'Table'[Month] ), DIVIDE ( amounttotal, maxperiodnumber ) )
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
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 ) )
THANK YOU Jihwan_Kim ! That was exactly the result i needed! appreciate it very much!