locked
selective roll up quesition RRS feed

  • Question

  • Hi,

    I have a hierachy and value defined as  for example location

    ParentLoc | ChildLoc | Value | isaggregateable

    Loc1 | Loc1.1  | 12 | 1

    Loc1 | Loc1.2 | 20 | 0

    Loc1 | Loc 1.3 | 23 | 1

    Loc2 | Loc2.1 | 12 | 1

    Loc2 | Loc2.2 |40 | 1

    Loc2 | Loc2.3 | 23 | 0

    Business rule says that only childLoc with isaggreagatble as 1 should aggreate to ParentValue in pivot table.

    result shuld be

    +Loc1 : 35

         Loc1.1:  12

        loc 1.2 : 20

        loc1.3  : 13

    +Loc2 : 52

        Loc2.1: 12

        loc 2.2 : 40

        loc2.3 : 23

    How should i define my measure

    Thanks in adavance


    Monday, April 21, 2014 9:04 AM

Answers

  • Ricky, this measure should deal with this very particular question, it may need to be modified for wider application!

    =
    IF (
        HASONEVALUE ( TABLE[ChildLoc] ),
        SUM ( TABLE[Value] ),
        CALCULATE (
            SUM ( TABLE[Value] ),
            TABLE[isaggregateable] = 1
        )
    )
    

    Jacob


    Jacob | Please mark helpful posts and answers

    Monday, April 21, 2014 2:47 PM