Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Andrea_Jess
Helper III
Helper III

Return max row count

Hi All, 

 

I have a table like the following: 

 

MonthCost CentrePay Period Number 

1/7/21

XX11
1/7/21XX12
1/7/21XX13

1/8/21

 XX14

1/8/21

 XX15

1/9/21

 XX16

 

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!

 

 

1 ACCEPTED SOLUTION

Picture1.png

 

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

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Go to My LinkedIn Page


View solution in original post

10 REPLIES 10
Jihwan_Kim
Super User
Super User

Hi, 

I am not sure if I understood your question correctly, but please check the below picture and the measure.

 

Picture1.png

 

Row Count Measure: =
COUNTROWS('Table' )
 

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Go to My LinkedIn Page


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

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 ) )
 
Picture1.png

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Go to My LinkedIn Page


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

Picture1.png

 

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

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Go to My LinkedIn Page


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

@Andrea_Jess You can use AVERAGEX for this:

 

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


Please @mention me in your reply if you want a response.

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

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 😞 

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


Please @mention me in your reply if you want a response.

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

AllisonKennedy
Super User
Super User

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


Please @mention me in your reply if you want a response.

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.