## Desktop

Frequent Visitor
Posts: 20
Registered: ‎07-28-2017

# Monthly Averages

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.

Accepted Solutions
Super User
Posts: 2,257
Registered: ‎09-19-2016

## Re: Monthly Averages

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

Proud to be a Datanaut!

All Replies
Super User
Posts: 2,257
Registered: ‎09-19-2016

## Re: Monthly Averages

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

Proud to be a Datanaut!

Highlighted
Frequent Visitor
Posts: 20
Registered: ‎07-28-2017

## Re: Monthly Averages

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.

Super User
Posts: 2,257
Registered: ‎09-19-2016

## Re: Monthly Averages

Are you using a calendar table?

Proud to be a Datanaut!

Super User
Posts: 2,257
Registered: ‎09-19-2016

## Re: Monthly Averages

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

Proud to be a Datanaut!

Frequent Visitor
Posts: 20
Registered: ‎07-28-2017

## Re: Monthly Averages

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.

Frequent Visitor
Posts: 20
Registered: ‎07-28-2017

## Re: Monthly Averages

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