# DAX Average calculation • ### 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

• 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 Monday, April 15, 2013 10:11 AM
Monday, April 15, 2013 10:00 AM

### 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:

hth,
gerhard

- www.pmOne.com -

Friday, April 12, 2013 9:09 AM
• 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
• 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 Monday, April 15, 2013 10:11 AM
Monday, April 15, 2013 10:00 AM
• 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
• 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
• 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
• 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