Answered by:
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.
 Edited by the_txeriff Friday, April 12, 2013 8:58 AM
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 monthlevelon yearlevel [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 7thats why you see different values for "prueba3" and "prueba4" on yearlevel
but the same values on monthlevel www.pmOne.com 
 Marked as answer by the_txeriff Monday, April 15, 2013 10:11 AM
Monday, April 15, 2013 10:00 AMAnswerer
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/scopingatdifferentgranularitiesindaxparti/
http://javierguillen.wordpress.com/2012/05/04/scopingatdifferentgranularitiespartii/
http://javierguillen.wordpress.com/2012/05/21/scopingatdifferentgranularitiespartiii/ www.pmOne.com 
Friday, April 12, 2013 9:09 AMAnswerer 
Although I bow to Gerhard's superior knowledge, this might be simpler :)
=AVERAGEX( VALUES(Dates[Month]), CALCULATE(SUM('Valuetable'[Value])/COUNTROWS(Dates) ) )
 Edited by Jacob Barnett Friday, April 12, 2013 11:50 AM
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 populatedthats 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 PMAnswerer 
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 monthlevelon yearlevel [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 7thats why you see different values for "prueba3" and "prueba4" on yearlevel
but the same values on monthlevel www.pmOne.com 
 Marked as answer by the_txeriff Monday, April 15, 2013 10:11 AM
Monday, April 15, 2013 10:00 AMAnswerer 
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 PMAnswerer 
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 commaseperator for your language settings)
 www.pmOne.com 
Monday, April 15, 2013 1:03 PMAnswerer 
the problem may be related to the last row
for some reason you have "." instead of "," (whatever is the correct commaseperator 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 JanMarch?
do we simply not have data for that periods? www.pmOne.com 
Monday, April 15, 2013 3:03 PMAnswerer 
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 PMAnswerer