cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
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
Super User IV
Super User IV

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



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

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

View solution in original post

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
secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

Wave Release 2

Check out the updates in Power BI.

Overview of Power BI 2020 release wave 2!

Microsoft Ignite

Microsoft Ignite

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors