locked
DAX Average calculation RRS feed

  • Question

  • Hi All,

    Here I am again.

    Well, I have been asked to make the following average:

    let´s say I have the following:

    I´d like to calculate average of each month (dividing by 31 days) and then sum those averages and divide by 12.

    I dont want to create 12 calculations for 12 months, so I wonder if there is any way to do it in a single DAX.

    by using next calculation it shows me the average per month (i know it´s always divided by 31 but I don´t know how to divide according to the number of days of the month. Countrows of days in that month maybe? how do I make that?). I´d like to sum the average of each month of the current context year and divide by 12.

    test2:=CALCULATE(SUM([Amount LCY]);DATESBETWEEN(Dates[PK_Date];
                                    FIRSTDATE(Dates[PK_Date]);
                                     LASTDATE(Dates[PK_Date])
                                       ))/31

    

    thanks a lot for your responses.

    


    Friday, April 12, 2013 8:33 AM

Answers

  • the following applies to both calculations ("prueba3" and "prueba4")

    VALUES(Dates[Month]) returns the months in the current slice (=filtercontext)
    so if you are on a single month, only one row is returned
    also [Cnt_Months] will return the value "1" then
    and so the result is divided by "1" on month-level

    on year-level [Cnt_Months] is 12 and so the value is also divided by 12
    AVERAGEX operates over 7 rows/monts (Month 4 till 12) and so the value is divided by 7

    thats why you see different values for "prueba3" and "prueba4" on year-level
    but the same values on month-level


    - www.pmOne.com -

    • Marked as answer by the_txeriff Monday, April 15, 2013 10:11 AM
    Monday, April 15, 2013 10:00 AM
    Answerer

All replies

  • this one works just fine for me:

    MyAverage:=SUMX(
    SUMMARIZE(
        Dates,
        Dates[Month],
        "AvgMonth", [Sum_Value]/[Cnt_Days]),
    [AvgMonth])
    / [Cnt_Months]

    Sum_Value:=SUM(ValueTable[Value])
    Cnt_Days:=COUNTROWS(Dates)
    Cnt_Months:=DISTINCTCOUNT(Dates[Month])

    here are some very good post about that topic:

    http://javierguillen.wordpress.com/2012/05/02/scoping-at-different-granularities-in-dax-part-i/
    http://javierguillen.wordpress.com/2012/05/04/scoping-at-different-granularities-part-ii/
    http://javierguillen.wordpress.com/2012/05/21/scoping-at-different-granularities-part-iii/

    hth,
    gerhard


    - www.pmOne.com -

    Friday, April 12, 2013 9:09 AM
    Answerer
  • Although I bow to Gerhard's superior knowledge, this might be simpler :-)

    =AVERAGEX(
              VALUES(Dates[Month]),
                 CALCULATE(SUM('Valuetable'[Value])/COUNTROWS(Dates)
                           )
              )


    Friday, April 12, 2013 11:40 AM
  • the AVERAGEX did not work for the sample provided as it only divided by 2 (=2 populated months)
    as only 2 months were populated

    thats why i switched back to SUM/COUNT

    in the end the expression could be simplified to this:

    MyAverage v2:=SUMX(
        VALUES(Dates[Month]);
        [Sum_Value]/[Cnt_Days])
    / [Cnt_Months]
    (of course you can further replace all [measures] by there original calculation using CALCULATE)


    - www.pmOne.com -

    Friday, April 12, 2013 12:45 PM
    Answerer
  • Hi all

    Thanks to both of you for the response. I added your calculations. prueba2 is my measure. "Prueba 3" is:

    MyAverage v2:=SUMX(
        VALUES(Dates[Month]);
        [Sum_Value]/[Cnt_Days])
    / [Cnt_Months]

    and "prueba 4"

    =AVERAGEX(
              VALUES(Dates[Month]),
                 CALCULATE(SUM('Valuetable'[Value])/COUNTROWS(Dates)
                           )
              )

    I dont really see why the totals are shown like that...

    sum of 12 months it´s 690,3585699. /12=57,52.

    By the way, I´d like to understand the DAX sentence. I assume that I´m selecting the different month values() and dividing the sum()/days of current month in context, divided by nº of months.

    So if I´m in january 2012: (3000/30)/12=8,3333333. I don´t really see how it does the "/12" divided.

    thanks a lot a again for your responses.

    Monday, April 15, 2013 8:10 AM
  • the following applies to both calculations ("prueba3" and "prueba4")

    VALUES(Dates[Month]) returns the months in the current slice (=filtercontext)
    so if you are on a single month, only one row is returned
    also [Cnt_Months] will return the value "1" then
    and so the result is divided by "1" on month-level

    on year-level [Cnt_Months] is 12 and so the value is also divided by 12
    AVERAGEX operates over 7 rows/monts (Month 4 till 12) and so the value is divided by 7

    thats why you see different values for "prueba3" and "prueba4" on year-level
    but the same values on month-level


    - www.pmOne.com -

    • Marked as answer by the_txeriff Monday, April 15, 2013 10:11 AM
    Monday, April 15, 2013 10:00 AM
    Answerer
  • thansk for the explanation Gerard.
    Monday, April 15, 2013 10:08 AM
  • Just one question:

    I still dont see how the totals per year are calculated. I cant make it with the calculator.

    Can you explain? are they correct?

    Monday, April 15, 2013 10:11 AM
  • for "pruebo3" (same also for "pruebo4")

    the monthly values are summed up

    so (100 + 9,677 + 526,400 + 78,907 + ...)

    this value is then divided by 12 ("pruebo3") or divided by 7 ("pruebo4")


    - www.pmOne.com -

    Monday, April 15, 2013 12:03 PM
    Answerer
  • yep... but for some reason the sum/12 doesnt fit (its very close) to the total per year... Do you think it´s because decimal places?

    Monday, April 15, 2013 12:33 PM
  • the problem may be related to the last row

    for some reason you have "." instead of "," (whatever is the correct comma-seperator for your language settings)


    - www.pmOne.com -

    Monday, April 15, 2013 1:03 PM
    Answerer
  • the problem may be related to the last row

    for some reason you have "." instead of "," (whatever is the correct comma-seperator for your language settings)


    - www.pmOne.com -

    thats the screen capture I cut accidentally.

    Monday, April 15, 2013 1:04 PM
  • the "pruebo4" calculation works just fine and the resulsts are reproduceable by using the SUM of the single month and divide it by 9 (not 7, this was a little mistake in my previous posts)

    for some reason the "pruebo3" cannot be reproduced, I dont know yet why

    btw, is there any specific reason why we do not see Jan-March?
    do we simply not have data for that periods?


    - www.pmOne.com -

    Monday, April 15, 2013 3:03 PM
    Answerer
  • yep I noticed that it was 9, no problem. We don´t see all months because no data on them all.

    so you don´t know where those totals come from neither?

    ack, strange behavior of the ssas tabular...

    Monday, April 15, 2013 3:06 PM
  • for "pruebo3" you may try adding [Cnt_Months] and [Cnt_Days] to the pivot
    they should help reproducing the result

    - www.pmOne.com -

    Monday, April 15, 2013 3:17 PM
    Answerer