Daily average in a day, not month, context

[calerof]

Hello all,

I have the following data:

Date	Qty	Avg Qty
01/04/2016	4456	4456
02/04/2016	4391	4391
04/04/2016	540	540
05/04/2016	34694	34694
06/04/2016	3445	3445
07/04/2016	4328	4328
08/04/2016	2790	2790
09/04/2016	1973	1973
11/04/2016	1741	1741
12/04/2016	4613	4613
13/04/2016	2436	2436
14/04/2016	5314	5314
15/04/2016	2970	2970
16/04/2016	2427	2427
18/04/2016	696	696
19/04/2016	4408	4408
20/04/2016	19650	19650
21/04/2016	3820	3820
22/04/2016	5683	5683
23/04/2016	4491	4491
25/04/2016	953	953
26/04/2016	3971	3971
27/04/2016	5475	5475
28/04/2016	6523	6523
29/04/2016	5269	5269
30/04/2016	3491	3491
Total	140548	5405.692308

I want to present the quantity in columns in a "line and stack column chart" and the total average by day, i.e. 5406 as a line. My average quantity formula goes like follows:

Avg_Qty = AVERAGEX(
		'Calendar',
		CALCULATE(SUMX(Estaciones, Estaciones[Quantity]))
)

But as my chart is in a day context, the average gets calculated by day, not by month. This is what I see:

Line and stacked column chart

The line in yellow appears in the day context, as it is my chart's goal for the quantity. But I'd like to show in this graph the total month daily average of 5406 as a straight horizontal line. Could you please show some light on how to accomplish this?

I would appreciate a lot your support.

Regards,

Fernando

[MattAllington]

Do you have a MonthID in your calendar table?  If not, you should get one so you can modify the filter context easily. 

Read my article about calendar table ID columns here http://exceleratorbi.com.au/power-pivot-calendar-tables/

assuming you add a MonthID column to your calendar table, you could write an average formula like this. 

Monthly Avg= Calculate([total Qty],filter(all(calendar),calendar[monthID]=max(calendar[MonthID])))

[calerof]

Hi @MattAllington,

Thank you very much for your response. Two things, one good and one bad. I made a slight modification to your suggestion and it worked great in a table. When coding as you suggested the result was the total quantity, not the average. So the code ended as follows:

Avg_Qty = AVERAGEX(
		FILTER(ALL('Calendar'),
                'Calendar'[MonthNumber] =MAX('Calendar'[MonthNumber])),
		CALCULATE(
			SUMX(Estaciones, Estaciones[Quantity]) 
		)
)

This gave me the following table:

Avg_Qty_suggested is your code, and Cant_Prom is with the modification

That was the good part. But when I included the measure in the chart, it keeps the daily shape, not the total month daily average. Like follows:

The line does not take the value of 5406 as in the table

[Vvelarde]

hi @calerof

Use in Shared Axis the date column from your calendar table.

[calerof]

@Vvelarde,

Thank you for your response. That's how it is now, see:

[MattAllington · Accepted solution]

---

@calerof wrote:

@Vvelarde,

Thank you for your response. That's how it is now, see:

---

You have the wrong date column in your visualisation.  Remove the Date from your data table and use the date from your Calendar table.  It is best to hide the date from your data table to avoid confusion

[calerof]

@MattAllington,

Brilliant!, thanks so much!

Average daily quantity

Fernando