Answered by:
DAX Sum of children
Question

Hi guys,
I'm working on a SSAS 2012 Tabular Model and am running into some difficulties with one of my measures.
What my setup looks like:Dim Time hierarchy: Year  Season (Quarter)  Month
Fact Forecast: Account  Material  Month  Forecast Quantity  Bookings Quantity
I now need to calculate the Forecast accuracy but scoped to the period shown.
On a Month level, this is working by doing the following:Forecast Accuracy:=1 (SUMX('Forecast',ABS(Forecast Quantity  Bookings Quantity))/Forecast Quantity)
My problem here starts on a higher grain, like Season or Year.
The biggest problem here is this part:ABS(Forecast Quantity  Bookings Quantity)
Both quantities should first be aggregated to Account  Material  <Period> level and then subtracted from eachother but I'm not able to get this to work.
Has anyone encountered this before because I don't have a clue how to solve this after crawling the web for half a day...
Kind regards,
Jan
Friday, April 11, 2014 1:11 PM
Answers

try this one
SUMX( SUMMARIZE( 'Fact Forecast', 'Fact Forecast'[Account], 'Fact Forecast'[Material]), CALCULATE ( ABS ( SUM ( [Forecast Qty] )  SUM ( [Bkgs Qty]) ) ) )
first group by Account and Material, calculate the ABSvalue on that level and use SUMX to sum up the results
hth,
gerhardGerhard Brueckl
blogging @ http://blog.gbrueckl.at
working @ http://www.pmOne.com Marked as answer by Jan Van humbeek Monday, April 14, 2014 8:11 AM
Monday, April 14, 2014 7:49 AMAnswerer
All replies

Jan,
Could you give some examples of data (can be made up) and what you are getting and what you would expect.
Friday, April 11, 2014 3:33 PM 
Try this:
Forecast Accuracy :=
1  (
SUMX (
VALUES ( 'Date'[Quarter] ),
CALCULATE (
ABS (
SUM ( Forecast[Quantity] )  SUM ( Bookings[Quantity] )
)
)
/ SUM ( Forecast[Quantity] )
)
)Marco Russo http://www.sqlbi.com http://www.powerpivotworkshop.com http://sqlblog.com/blogs/marco_russo
 Proposed as answer by Michael Amadi Friday, April 11, 2014 5:06 PM
 Unproposed as answer by Jan Van humbeek Monday, April 14, 2014 6:27 AM
Friday, April 11, 2014 4:33 PM 
Hi Marco,
Thanks for your response, I first tried implementing the Forecast Error by slightly altering the measure as you suggested:
Forecast Error:=
SUMX (
VALUES ( Time[Business Season] ),
CALCULATE (
ABS (
SUM ( [Forecast Qty] )  SUM ( [Bkgs Qty])
)
)
)However, this does not give me the correct information either...
On the lowest level this works as expected like my measures tried beforehand.
Once I start rolling up, the calculations are happening incorrectly.For example, in the image below, for the account *test* it gives Forecast Error 76450 total, but should return 482152 which is the sum of all the values on the lowest level (entire range selected in Excel here for demonstration).
Any thoughts on this?
Kind regards,
Jan
Monday, April 14, 2014 6:36 AM 
try this one
SUMX( SUMMARIZE( 'Fact Forecast', 'Fact Forecast'[Account], 'Fact Forecast'[Material]), CALCULATE ( ABS ( SUM ( [Forecast Qty] )  SUM ( [Bkgs Qty]) ) ) )
first group by Account and Material, calculate the ABSvalue on that level and use SUMX to sum up the results
hth,
gerhardGerhard Brueckl
blogging @ http://blog.gbrueckl.at
working @ http://www.pmOne.com Marked as answer by Jan Van humbeek Monday, April 14, 2014 8:11 AM
Monday, April 14, 2014 7:49 AMAnswerer 
Thanks Gerhard!
That SUMMARIZE() function was still new to me and didn't exactly know how I should use it...
Monday, April 14, 2014 8:13 AM