Forum Discussion

josepcervello's avatar
josepcervello
Frequent Visitor
7 years ago

Parent aggregation getting last child value

Hi,    I created a Pivot table based on Switch formula and measures that are applied depending on the "level1-level2" name.   Why paret aggregations are just getting the last value of the childs?...
Need Help
Mariusz's avatar
Mariusz
Icon for Community Champion rankCommunity Champion
7 years ago

Hi josepcervello 

Can you include the formula?

Many Thanks
Mariusz

josepcervello's avatar
josepcervello
Frequent Visitor
7 years ago

I pass you all the info:

The table config:

 

The formula:

(every row is a sum measure calculated in a measures table)

 

The hierarchy is built as following:

 

Thank you very much in advance.

  • Mariusz's avatar
    Mariusz
    Icon for Community Champion rankCommunity Champion
    7 years ago

    Hi josepcervello 

    You need to add all the measures when on TIS-Level1 Something like below.

    Measure 2 = 
VAR h2 = SELECTEDVALUE('dynamic column'[Index])
VAR h1 = SELECTEDVALUE('dynamic column'[Column 2])
VAR hh = IF(ISBLANK(h2), h1, h1 & " - " & h2) 
VAR py = [POPS BTS Previous Year] + [POPS Decommissioning Previous Year] -- + all other Previous Year Measures
VAR cy = [POPS BIS Current Year] + -- + all current year measures
RETURN 
SWITCH (
    hh,
    "POPS Previous Year", py,
    "POPS Current Year", cy,
    "POPS Previous Year - POPS POPS BTS Previous Year", [POPS BTS Previous Year]
    -- Rest of the rules  
)

    Regards,
    Mariusz

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • josepcervello's avatar
      josepcervello
      Frequent Visitor
      7 years ago
      What are these coulmns refering to? The hierarchy table?

      'dynamic column'[Index]
'dynamic column'[Column 2]

      Thank you,

       

      • Mariusz's avatar
        Mariusz
        Icon for Community Champion rankCommunity Champion
        7 years ago

        Hi josepcervello 

        Sorry my bad, SELECTEDVALUE replaces your MAX's in the SWITCH as below.

         

        Measure 2 = 
VAR h2 = SELECTEDVALUE(YourTable[TIS-Level2])
VAR h1 = SELECTEDVALUE(YourTable[TIS-Level1])
VAR hh = IF(ISBLANK(h2), h1, h1 & " - " & h2) 
VAR py = [POPS BTS Previous Year] + [POPS Decommissioning Previous Year] -- + all other Previous Year Measures
VAR cy = [POPS BIS Current Year] + -- + all current year measures
RETURN 
SWITCH (
    hh,
    "POPS Previous Year", py,
    "POPS Current Year", cy,
    "POPS Previous Year - POPS POPS BTS Previous Year", [POPS BTS Previous Year]
    -- Rest of the rules  
)

         

        Regards,
        Mariusz

        If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.