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
Hussain
Helper I
Helper I

Measure to calculate average every month

Hi,

I have a table like this:

 

idDateCategoryLocation
11/1/2020RedUS
21/1/2020RedUK
41/2/2020YellowCanada
51/3/2020BlueMexico
61/3/2020RedGermany
71/4/2020GreenUK

 

Each record is an instance/transaction

I have to calculate the average of the  occurence of/number of transactions for each Category. A month may have data for a few days only or may have data for the whole month (like February will have data for only 15 days).

 

For example, I need to answer the question:

There were on average 20 transactions each day for the Category 'Red' in the Month of January 2020.

 

How can we do this using a measure? Is it necessary to evaluate count on each day first in a separate table(using summarize function_ or can we do it in 1 measure?

 

 

Thanks in advance!!

 

1 ACCEPTED SOLUTION

@Hussain hello Hussain. Yes the suggestion of month year is based on heirarchy.

So that if you have a date heriarchy you can have that in your date slicer in the report, that way when you filter by month your seeing that in a more granular view with year as additional context. So instead of looking at January for all January's in all years you are looking specifically at January for say 2019 for example.

 

Or by simply providing month year you can view in the slicer as for example jan-2019

Feb-2019..etc

 

If this answer helped, please mark it as a solution.

View solution in original post

6 REPLIES 6
amitchandak
Super User
Super User

@Hussain , Try a new measure Like

Assumed you have month year column

 

averagex( VALUES( Dates[Date] ), calculate( countrows( 'Table' ) ) )

or

calculate(averagex( VALUES( Dates[Date] ), calculate( countrows( 'Table' ) ) ), allexpcept(Table, Table[Month-Year]))

@amitchandak ,

 

I am using the formula AVERAGEX(VALUES('Dates'[Date]),CALCULATE(COUNTROWS('Table'))). My question is why doesn't this formula work without calculate function? I  mean like this:

AVERAGEX(VALUES('Dates'[Date]),COUNTROWS('Table'))?

Thanks!!

Hi,

The calculation function helps with context transition.  It switches from filter to row context.  Alternatively, you may try these 2 measures

Measure1 = COUNTROWS('Table')

Measure2 = AVERAGEX(VALUES('Dates'[Date]),[measure1])

Hope this helps.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Thanks Amit and @mpicca13 ,

What do  you mean by Month-Year Column? I have date column in the table. I can create a column for Month and Year in which the values are like these:

Jan-2020,Feb-2020(MMM-YYYY format or MM-YYYY format)?

Is this what you are suggesting?

 

@Hussain hello Hussain. Yes the suggestion of month year is based on heirarchy.

So that if you have a date heriarchy you can have that in your date slicer in the report, that way when you filter by month your seeing that in a more granular view with year as additional context. So instead of looking at January for all January's in all years you are looking specifically at January for say 2019 for example.

 

Or by simply providing month year you can view in the slicer as for example jan-2019

Feb-2019..etc

 

If this answer helped, please mark it as a solution.

mpicca13
Resolver IV
Resolver IV

@Hussain assuming you have a Month filter and Category filter in your page, try this formula,

 

Average Transaction Count =
AVERAGEX( VALUES( Dates[Date] ), CALCULATE( COUNTROWS( 'Transaction Table' ) ) )
 
If this answer helped, please mark my response as a solution.

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.