cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jigr69
Frequent Visitor

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:

 

DateTotal InTotal OutDaily AverageTotalMonth
27 September 20171077326497.6111037September
28 September 20171119838296.7011580September
29 September 201717753112294.0618875September
30 September 2017956864993.6510217September
28 October 20171143493892.4212372October
29 October 201715416096.251601October
30 October 2017918499.57922October
31 October 201785652499.728589October
01 November 20171145263594.7512087November
02 November 2017778553193.618316November

 

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:

 

DateTotal InTotal OutDaily AverageTotalMonthMonthly Average
27 September 20171077326497.6111037September96.00
28 September 20171119838296.7011580September96.00
29 September 201717753112294.0618875September96.00
30 September 2017956864993.6510217September96.00
28 October 20171143493892.4212372October95.63
29 October 201715416096.251601October95.63
30 October 2017918499.57922October95.63
31 October 201785652499.728589October95.63
01 November 20171145263594.7512087November94.29
02 November 2017778553193.618316November94.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.

1 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


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



View solution in original post

6 REPLIES 6
MFelix
Super User
Super User

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


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



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


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



jigr69
Frequent Visitor

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
Frequent Visitor

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

jigr69
Frequent Visitor

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.

Are you using a calendar table?

 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Helpful resources

Announcements
June 2022 update 768X460.jpg

Check it out!

Click here to read more about the June 2022 updates!

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Power BI Dev Camp Session 23 768x460.jpg

Check it Out!

Mark your calendars and join us on Thursday, June 30 at 11a PDT for a great session with Ted Pattison!

June 20 episode 7 with aka link 768x460.jpg

The Power BI Community Show

Join us on June 20 at 11 am PDT when Kim Manis shares the latest on Azure Synapse analytics, the Microsoft Intelligent Data Platform, and notable Power BI Updates from Microsoft Build 2022.

Top Solution Authors
Top Kudoed Authors