cancel
Showing results for
Did you mean:
Frequent Visitor

## 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?

Thank you

7 REPLIES 7
Super User IV

## Re: Parent aggregation getting last child value

Hi @josepcervello

Can you include the formula?

Many Thanks
Mariusz

Frequent Visitor

## Re: Parent aggregation getting last child value

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.

Highlighted
Super User IV

## Re: Parent aggregation getting last child value

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.

Frequent Visitor

## Re: Parent aggregation getting last child value

```What are these coulmns refering to? The hierarchy table?'dynamic column'[Index]
'dynamic column'[Column 2]Thank you,```

Super User IV

## Re: Parent aggregation getting last child value

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.

Frequent Visitor

## Re: Parent aggregation getting last child value

Based on your code, I should add to my hierarchy table two rows with a blank level 2, as I show in the image. If not, the code is not working...

Super User IV

## Re: Parent aggregation getting last child value

Hi @josepcervello,

It should work withot adding extra lines.

```VAR h2 = SELECTEDVALUE(YourTable[TIS-Level2])
VAR h1 = SELECTEDVALUE(YourTable[TIS-Level1])
VAR hh = IF(ISBLANK(h2), h1, h1 & " - " & h2)  -- this checks for level 2 selection, when you are in your table on a total level h2 is blank you can see only h1 ```

Thanks Mariusz
Regards,
Mariusz

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

Announcements

#### Coming Soon: T-Shirt Design Contest

Keep your eyes open for our upcoming T-shirt design contest!

#### Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

#### Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

#### Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!