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/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!
- 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 ) )
10 Replies
Anonymous 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.
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' )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 Anonymous You can use AVERAGEX for this:
AVERAGEX(VALUES(DimDate[Month]), [Measure])
