Forum Discussion

calerof's avatar
calerof
Icon for Impactful Individual rankImpactful Individual
10 years ago
Solved

Daily average in a day, not month, context

Hello all,

I have the following data:

 

DateQtyAvg Qty
01/04/201644564456
02/04/201643914391
04/04/2016540540
05/04/20163469434694
06/04/201634453445
07/04/201643284328
08/04/201627902790
09/04/201619731973
11/04/201617411741
12/04/201646134613
13/04/201624362436
14/04/201653145314
15/04/201629702970
16/04/201624272427
18/04/2016696696
19/04/201644084408
20/04/20161965019650
21/04/201638203820
22/04/201656835683
23/04/201644914491
25/04/2016953953
26/04/201639713971
27/04/201654755475
28/04/201665236523
29/04/201652695269
30/04/201634913491
Total1405485405.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

Need Help
  • MattAllington's avatar
    MattAllington
    10 years ago
    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

6 Replies

  • MattAllington's avatar
    MattAllington
    Icon for Community Champion rankCommunity Champion
    10 years ago

    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's avatar
      calerof
      Icon for Impactful Individual rankImpactful Individual
      10 years ago

      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's avatar
        Vvelarde
        Icon for Community Champion rankCommunity Champion
        10 years ago

        hi calerof

         

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