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

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

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

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

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

#### New Kudos Given Badges Coming

We're rolling out new Kudos Given badges. Find out how many Kudos you've given.

#### November 2019 Community Highlights

Get an overview of the events and great community content from November.

#### Power Platform World Tour

Find out where you can attend!

Top Solution Authors
Top Kudoed Authors (Last 30 Days)