Forum Discussion

Tribal's avatar
Tribal
Frequent Visitor
2 years ago
Solved

Combine switch functions - hierarchy

I'm currently building a matrix visual where I'm using two tables given below (trending table and waterfall_switch_table) as a basis to switch to required variable using the following measure. 

 

 

 

Trending Measure = 
VAR B1 = 
CALCULATE(
    SUM(
        'Bridge Table (CMA05 - Budgetvolume)'[B1]
    )
)

VAR B2 = 
CALCULATE(
    SUM(
        'Bridge Table (CMA05 - Budgetvolume)'[B2]
    )
)

VAR B3 = 
CALCULATE(
    SUM(
        'Bridge Table (CMA05 - Budgetvolume)'[B3]
    )
)

VAR B4 = 
CALCULATE(
    SUM(
        'Bridge Table (CMA05 - Budgetvolume)'[B4]
    )
)

VAR B5 = 
CALCULATE(
    SUM(
        'Bridge Table (CMA05 - Budgetvolume)'[B5]
    )
)

VAR B6 = 
CALCULATE(
    SUM(
        'Bridge Table (CMA05 - Budgetvolume)'[B6]
    )
)

VAR B7 = 
CALCULATE(
    SUM(
        'Bridge Table (CMA05 - Budgetvolume)'[B7]
    )
)

VAR B8 = 
CALCULATE(
    SUM(
        'Bridge Table (CMA05 - Budgetvolume)'[B8]
    )
)

VAR B9 = 
CALCULATE(
    SUM(
        'Bridge Table (CMA05 - Budgetvolume)'[B9]
    )
)

VAR B10 = 
CALCULATE(
    SUM(
        'Bridge Table (CMA05 - Budgetvolume)'[B10]
    )
)

VAR B11 = 
CALCULATE(
    SUM(
        'Bridge Table (CMA05 - Budgetvolume)'[B11]
    )
)

VAR B12 = 
CALCULATE(
    SUM(
        'Bridge Table (CMA05 - Budgetvolume)'[B12]
    )
)

VAR B13 = 
CALCULATE(
    SUM(
        'Bridge Table (CMA05 - Budgetvolume)'[B13]
    )
)

VAR A1 = 
CALCULATE(
    SUM(
        'Bridge Table (CMA05 - Budgetvolume)'[A1]
    )
)

VAR A5 = 
CALCULATE(
    SUM(
        'Bridge Table (CMA05 - Budgetvolume)'[A2]
    )
)

VAR A2 =
B1 + B2 + B3 + B4 + B5 + B6 + B7

VAR A3 = 
B8 + B9 + B10

VAR A4 =
B11 + B12 + B13

RETURN
IF(
    ISINSCOPE(Trending[Sub var]),
    SWITCH (
        SELECTEDVALUE( Trending[ID] ),
        1, B1,
        2, B2,
        3, B3,
        4, B4,
        5, B5,
        6, B6,
        7, B7,
        8, B8,
        9, B9,
        10, B10,
        11, B11,
        12, B12,
        13, B13
    ),
    SWITCH (
        SELECTEDVALUE( Waterfall_SwitchTable[ID] ),
        1, A1,
        2, A2,
        3, A3,
        4, A4,
        5, A5
    )
)

 

 

 

This produces the following matrix, where the values are shown when expanded to level B. But consequently all level A values are hidden and will only be shown when the table is fully collapsed. I've been trying to combine these two switch functions, but haven't been able to unfortunately. Could someone steer me into the right direction to make this work?

Expanded:


Collapsed:

 

Trending table:

 

Waterfall_Switch_table:

 

Sample data - Budgetvolume table:

A1A5B1B2B3B4B5B6B7B8B9B10B11B12B13
20303456789101112131415
20303456789101112131415
20303456789101112131415
20303456789101112131415
20303456789101112131415
20303456789101112131415

 

4 Replies