Search code examples
ssasbusiness-intelligencetabulardax

DAX Sum of children

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 - 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...

Solution

  • Answered on MSDN By Gerhard Brueckl:

    Forecast Error:=
    SUMX(
        SUMMARIZE(
            'Fact Forecast',
            'Fact Forecast'[Account],
            'Fact Forecast'[Material]),
        CALCULATE (
            ABS (
                SUM ( [Forecast Qty] ) - SUM ( [Bkgs Qty])
            )
        )
    )