locked
DAX Sum of children RRS feed

  • 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 ABS-value on that level and use SUMX to sum up the results

    hth,
    gerhard


    Gerhard Brueckl
    blogging @ http://blog.gbrueckl.at
    working @ http://www.pmOne.com

    Monday, April 14, 2014 7:49 AM
    Answerer

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

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

    Example data

    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 ABS-value on that level and use SUMX to sum up the results

    hth,
    gerhard


    Gerhard Brueckl
    blogging @ http://blog.gbrueckl.at
    working @ http://www.pmOne.com

    Monday, April 14, 2014 7:49 AM
    Answerer
  • 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