Power BI : Monthly averages

Question

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" divided 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.

Answer 1

Create a DAX using the EARLIER function should do the trick:

Month Average = 
VAR sum_total_in = 
CALCULATE(
    SUM('table'[Total In]),
    FILTER(
        'table',
        'table'[Month] = EARLIER('table'[Month])
    )
)

VAR sum_total = 
CALCULATE(
    SUM('table'[Total]),
    FILTER(
        'table',
        'table'[Month] = EARLIER('table'[Month])
    )
)

RETURN sum_total_in / sum_total

So basically it sums the Total In and Total within the same month and returns the division.

Results:

P.S. You'll need to add the year to the filter as well if you have data across years with the same month.

Answer 2

I see you're asking your question from a DAX perspective, but here's a Power Query based answer that's quite easy. Starting with your table in Power Query (Power BI's Query Editor):

Select the Month column, then Transform -> Group By, and set up the dialog box like this...

and click OK.

You'll see this:

Then click the button at the top of the AllData column to expand the nested tables, and set up the dialog box like this...

and click OK.

You'll see this:

Now click Add Column -> Custom Column, and set up the dialog box like this...

and click OK.

You'll see this:

Now select the first three columns (Month, Sum Total In, Sum Total) and click Home -> Remove Columns.

You'll see this:

You can double-click the Month.1 column title and rename it if you like.