cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
josepcervello Frequent Visitor
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
Super User IV

Re: Parent aggregation getting last child value

Hi @josepcervello 

Can you include the formula?

Many Thanks
Mariusz

josepcervello Frequent Visitor
Frequent Visitor

Re: Parent aggregation getting last child value

I pass you all the info:

The table config:

2019-05-17 15_56_33-CellnexV4 (1) - Power BI Desktop.png

 

The formula:

2019-05-17 15_55_36-CellnexV4 (1) - Power BI Desktop.png

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

 

The hierarchy is built as following:

2019-05-17 12_31_33-Create Table.png

 

Thank you very much in advance.

Highlighted
Super User IV
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.

josepcervello Frequent Visitor
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
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.

josepcervello Frequent Visitor
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...


2019-05-17 17_44_49-CellnexV4 (1) - Power BI Desktop.png

Super User IV
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.

Helpful resources

Announcements
Coming Soon: T-Shirt Design Contest

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!

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?

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

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!

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors