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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

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

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here

@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
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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