Measure to calculate average every month

[Hussain]

Hi,

I have a table like this:

id	Date	Category	Location
1	1/1/2020	Red	US
2	1/1/2020	Red	UK
4	1/2/2020	Yellow	Canada
5	1/3/2020	Blue	Mexico
6	1/3/2020	Red	Germany
7	1/4/2020	Green	UK

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

[amitchandak]

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

[Hussain]

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

[Ashish_Mathur]

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.

[Hussain]

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?

[mpicca13 · 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.

[mpicca13]

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