Forum Discussion

jigr69's avatar
jigr69
Frequent Visitor
8 years ago
Solved

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 contain...
Need Help
  • MFelix's avatar
    MFelix
    8 years ago

    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

MFelix's avatar
MFelix
Super User
8 years ago

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's avatar
MFelix
Super User
8 years ago

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's avatar
    jigr69
    Frequent Visitor
    8 years ago

    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's avatar
      jigr69
      Frequent Visitor
      8 years ago

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