locked
Cumulative Average in Power Pivot RRS feed

  • Question

  • Hello,

    Currently, I have this cumulative DAX below :

    = CALCULATE(
        SUM(tab[Value]),
        FILTER(
            ALL(tab[Date]),
            tab[Date] <= MAX(tab[Date])
        )
    )


    Which represents this calculations :
    January  : Value_Month1
    February : (Value_Month2 + Value_Month1)
    March    : (Value_Month3 + Value_Month2 + Value_Month1)
    April    : (Value_Month4 + Value_Month3 + Value_Month2 + Value_Month1)
    ...


    ... it works great but now my boss wants this :
    January  : Value_Month1
    February : (Value_Month2 + Value_Month1) / 2
    March    : (Value_Month3 + Value_Month2 + Value_Month1) / 3
    April    : (Value_Month4 + Value_Month3 + Value_Month2 + Value_Month1) / 4
    ...


    I would say it looks like an "Average Cumulative" (I don't know how to call it) but I don't know how to DAX it, could you help me with this new formula please ?

    Thanks,

    Wednesday, June 3, 2015 8:22 AM

Answers

  • Hi guk92,

    Following on from your approach you could add another Calculated Field that returns the cumulative months:

    =
    CALCULATE(
        COUNTROWS(tab),
        FILTER(
            ALL(tab[Date]),
            tab[Date] <= MAX(tab[Date])
        )
    )

    And then simply divide your cumulative value Calculated Field by this new cumulative month count one:

    =DIVIDE([CumulativeValue], [CumulativeMonths])

    Regards,

    Michael Amadi

    Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to vote it as helpful :)

    Website: http://www.nimblelearn.com, Twitter: @nimblelearn

    Wednesday, June 3, 2015 2:21 PM
  • Hi guk92,

    Assuming that you want the behaviour shown below in the 'CumulativeAverageValue2' Calculated Field in this image...

    ...You could create another Calculated Field with DAX similar to the following...

    CumulativeAverageValue2:=
    IF(
      HASONEVALUE(tab[Date]),
      [CumulativeAverageValue],
      [CumulativeValue]
    )

    In the above DAX example, 'CumulativeAverageValue' refers to the Calculated Field that was suggested in my earlier post. The resulting behaviour does seem a bit unusual (when considering that the original aim was a cumulative monthly average).


    Regards,

    Michael Amadi

    Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to vote it as helpful :)

    Website: http://www.nimblelearn.com, Twitter: @nimblelearn

    Saturday, June 20, 2015 2:41 PM

All replies

  • Does Date column contain every dates in a month or just month dates?

    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Wednesday, June 3, 2015 8:50 AM
  • tab[Date] is DateTime type, but only contains the first day of each months and years with this format DAY/MONTH/YEAR. Example :

    01/01/2014

    01/02/2014

    01/03/2014

    01/04/2014

    01/05/2014

    ...

    Wednesday, June 3, 2015 9:04 AM
  • Hi guk92,

    Following on from your approach you could add another Calculated Field that returns the cumulative months:

    =
    CALCULATE(
        COUNTROWS(tab),
        FILTER(
            ALL(tab[Date]),
            tab[Date] <= MAX(tab[Date])
        )
    )

    And then simply divide your cumulative value Calculated Field by this new cumulative month count one:

    =DIVIDE([CumulativeValue], [CumulativeMonths])

    Regards,

    Michael Amadi

    Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to vote it as helpful :)

    Website: http://www.nimblelearn.com, Twitter: @nimblelearn

    Wednesday, June 3, 2015 2:21 PM
  • Hi,

    Thank you for your answer Michael Amadi, this is the formula I was looking for but I have a problem with it.

    When I use your formula the elements are good, but it create an implicit average for the elements like this :

    • PowerPivot shows me : 4.26 (Average) for 1.24 | 7.04 | 6.55 | 3.64 | 4.04 | 3.04
    • But I want this : 25.55 (Sum) for 1.24 | 7.04 | 6.55 | 3.64 | 4.04 | 3.04

    Thanks

    Tuesday, June 9, 2015 1:15 PM
  • Hi guk92,

    Is there some sample data that you can provide or mock-up along with the expected output? Providing this will make it easier to refine the DAX formula to give the expected behaviour.


    Regards,

    Michael Amadi

    Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to vote it as helpful :)

    Website: http://www.nimblelearn.com, Twitter: @nimblelearn

    Wednesday, June 10, 2015 11:50 PM
  • Can you also post the measures you are using. What Michael posted should work if you use something like
    MaxMonth:=FORMAT(MAX(tab[Date]),"mm") for your month division value
    Thursday, June 11, 2015 3:32 AM
  • Hi guk92,

    Assuming that you want the behaviour shown below in the 'CumulativeAverageValue2' Calculated Field in this image...

    ...You could create another Calculated Field with DAX similar to the following...

    CumulativeAverageValue2:=
    IF(
      HASONEVALUE(tab[Date]),
      [CumulativeAverageValue],
      [CumulativeValue]
    )

    In the above DAX example, 'CumulativeAverageValue' refers to the Calculated Field that was suggested in my earlier post. The resulting behaviour does seem a bit unusual (when considering that the original aim was a cumulative monthly average).


    Regards,

    Michael Amadi

    Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to vote it as helpful :)

    Website: http://www.nimblelearn.com, Twitter: @nimblelearn

    Saturday, June 20, 2015 2:41 PM