Return max row count

[Andrea_Jess]

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/9/21	XX1	6

I want to create either a measure  that basically counts the rows based on month and summarises the date by month.

i.e. for example, for Cost Centre XX1 in the month of 1/7/21, there are 3 pay periods so i want it to return 3. 

For 1/8/21, there are 2 pay periods, so i want to be able to return 2.

Does anyone know how i can do this? 

thank you!

[Jihwan_Kim]

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

[Andrea_Jess]

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

Month	Cost Centre	Period Number	Amount
1/7/21		1	5
1/7/21		2	34
1/7/21		3	2

[Jihwan_Kim]

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

[Andrea_Jess]

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 · Accepted solution]

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

[Andrea_Jess]

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

[AllisonKennedy]

@Andrea_Jess You can use AVERAGEX for this:

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

[Andrea_Jess]

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]

@Andrea_Jess  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!

[AllisonKennedy]

@Andrea_Jess  This is a simple COUNT measure if you have a DimDate table: 

https://excelwithallison.blogspot.com/2020/04/dimdate-what-why-and-how.html

Then relate the Date table to the Pay period in your table and create a measure that does:

Measure = COUNT(Table[Cost Centre]) 

Put that Measure in the Values and DimDate[Month] in the Axis of a visual.