Forum Discussion

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

Tips and Tricks

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

Icon for Community Champion rank

Community Champion

4 years ago

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.