Monthly Averages

[jigr69]

I know similar questions have been asked and answered previous to this, but for the life of me, cannot get any of those working with my limited knowledge on Power BI.

I have a table which contains 8 weeks worth of data which for most parts, spans 3 months. I currently calculate the daily average which is the "total" divded by the "total in". However what I need to do, is to display the average based upon the calendar month. Therefore, September will have a difference average to that of October and that of November. 

Here's a sample of the data:

Date	Total In	Total Out	Daily Average	Total	Month
27 September 2017	10773	264	97.61	11037	September
28 September 2017	11198	382	96.70	11580	September
29 September 2017	17753	1122	94.06	18875	September
30 September 2017	9568	649	93.65	10217	September
28 October 2017	11434	938	92.42	12372	October
29 October 2017	1541	60	96.25	1601	October
30 October 2017	918	4	99.57	922	October
31 October 2017	8565	24	99.72	8589	October
01 November 2017	11452	635	94.75	12087	November
02 November 2017	7785	531	93.61	8316	November

So for November I would like to have the figure as (11452 + 7785) / (12087 + 8316) * 100 = 94.29%. Obviously, that figure would be present for all dates in November as an extra column. For October it would be (11434 + 1541 + 918 + 8565) / (12372 + 1601 + 922 + 8589) * 100 = 95.63%. I would then use the extra column and plot it on a line/bar chart.

So the above data would become:

Date	Total In	Total Out	Daily Average	Total	Month	Monthly Average
27 September 2017	10773	264	97.61	11037	September	96.00
28 September 2017	11198	382	96.70	11580	September	96.00
29 September 2017	17753	1122	94.06	18875	September	96.00
30 September 2017	9568	649	93.65	10217	September	96.00
28 October 2017	11434	938	92.42	12372	October	95.63
29 October 2017	1541	60	96.25	1601	October	95.63
30 October 2017	918	4	99.57	922	October	95.63
31 October 2017	8565	24	99.72	8589	October	95.63
01 November 2017	11452	635	94.75	12087	November	94.29
02 November 2017	7785	531	93.61	8316	November	94.29

I am having trouble getting my head around the SUMMARIZE functions etc in order to get this working without any help. So any help and explanation would be greatly appreciated.

Thanks.

[MFelix]

Hi @jigr69,

Try this formula:

Monthly average =
DIVIDE (
    CALCULATE ( SUM ( In_Out[Total In] ), ALLEXCEPT ( In_Out, In_Out[Month] ) ),
    CALCULATE ( SUM ( In_Out[Total] ), ALLEXCEPT ( In_Out, In_Out[Month] ) )
)

Insert as measure.

Regards,

MFelix

[MFelix · Accepted solution]

If you are using a calendar table try using this:

Monthly average _ =
DIVIDE (
    CALCULATE (
        SUM ( In_Out[Total In] ),
        DATESBETWEEN (
            'calendar'[Date],
            STARTOFMONTH ( 'calendar'[Date] ),
            ENDOFMONTH ( 'calendar'[Date] )
        )
    ),
    CALCULATE (
        SUM ( In_Out[Total] ),
        ALLEXCEPT ( In_Out, In_Out[Month] ),
        DATESBETWEEN (
            'calendar'[Date],
            STARTOFMONTH ( 'calendar'[Date] ),
            ENDOFMONTH ( 'calendar'[Date] )
        )
    )
)

Regards,

Mfelix

[jigr69]

I have a calendar table but wasn't using it for this particular chart, I will try it and your solution below and let you know how it goes.

Thanks fror your help and input so far. 🙂

[jigr69]

Yes, it has worked brilliantly, thank you for your help!

[jigr69]

Hi MFelix.

I just tried that but it appears to be calculating the average for all of the months as one unit, i.e. giving the same figure for September, October and November, unfortunately.

[MFelix]

Are you using a calendar table?