Display dynamic 'reduction targets' across multi-level hierarchy in column chart

[JadeM]

Hi all, I would appreciate some advice on a problem I have been looking at for a while. This is to do with reporting on different sustainability targets.

I want to make a clustered column chart that shows both the performance of a company towards in reducing various measures (eg water use) and the reduction target (ie. reduce X by 50%).

The different targets have different sub-targets, so I have built a hierarchy.

One issue is that the main targets that will be shown in this column chart are at different levels of the hierarchy, I have therefore unpivoted the different levels to create one column called 'Value'.

• Examples of this (sample data below) are that 'GHG Emissions' and 'Waste' are Level 1 targets that need to be included, 'Heating' is a level 2 target that needs to be included and 'International flights' and 'domestic flights' are level 3 targets to be included in this column chart.
• I cannot change the hierarchy so that these values are all in the same level unfortunately! 

This approach works fine and I can see the appropriate target categories. The issue is with the reduction target figure that is also included in the clustered column chart (ie. reduce X by 50%). When I use drill down to see other levels in the hierarchy the target from the first level of the hierarchy propagates through to all lower levels. This is a problem because some of the sub targets have their own reduction target and I would like the chart to change to show the sub targets when drill down is used.

Example:

Target level 1 is GHG emissions: this has a reduction target of -50%

GHG emissions breaks down into a handful of different subcategories like cars, heating etc. 'Heating' is a sub target that falls under GHG emissions as Target level 2 and it has a reduction taregt of -60%.

So in this example I would initally like the reduction target column on the chart to show -50%, then when users drill down on that column I want to see 'Heating' to have a reduction target column of -60% (and other Target Level 2 categories to retain -50%)

How can I achieve this? I can restructure my data if needed.

This is my current set up (Many to One relationship between 'Fact' and 'Hierarchy' on the 'Hierarchy ID' column

Columns used in my column chart:

X-axis:

'Fact'[Value]

Target Hierarchy (this is the name of the hierarchy I built in Power BI)

'Hierarchy'[Targets Level 1]

'Hierarchy'[Targets Level 1]

'Hierarchy'[Targets Level 1]

Y-axis:

'Fact'[Reduction Target] (this is a measure I made below)

'Fact'[Performance]

Sample of the hierarchy table:

Targets Level 1	Targets Level 2	Targets Level 3	Hierarchy Key
GHG Emissions	Heating	Gas	1
GHG Emissions	Heating	Electric	2
GHG Emissions	Cars	Petrol Cars	3
GHG Emissions	Cars	Electric Cars	4
GHG Emissions	Flights	Domestic Flights	5
GHG Emissions	Flights	International Flights	6
Waste	Waste	Incinerated Waste	7
Waste	Waste	Food Waste	8
Waste	Waste	Landfill Waste	9
Water	Water Used		10

Sample of the Fact table:

Hierarchy Key	Attribute	Value	Reduction Target 1	Reduction Target 2	Reduction Target 3
1	Targets Level 1	GHG Emissions	-0.50	-0.60	-0.60
1	Targets Level 2	Heating	-0.50	-0.60	-0.60
1	Targets Level 3	Gas	-0.50	-0.60	-0.60
2	Targets Level 1	GHG Emissions	-0.50	-0.60	-0.60
2	Targets Level 2	Heating	-0.50	-0.60	-0.60
2	Targets Level 3	Electric	-0.50	-0.60	-0.60
3	Targets Level 1	GHG Emissions	-0.50	-0.50	-0.50
3	Targets Level 2	Cars	-0.50	-0.50	-0.50
3	Targets Level 3	Petrol Cars	-0.50	-0.50	-0.50

So far all I have thought of is using a measure like the below, but this does not work (obviously):

Reduction Target =
VAR CurrentLevel = SELECTEDVALUE('Fact'[Value])
VAR ReductionTarget1 = MAX('Fact'[Reduction Target 1])
VAR ReductionTarget2 = MAX('Fact'[Reduction Target 2])
VAR ReductionTarget3 = MAX('Fact'[Reduction Target 3])

RETURN
SWITCH(
TRUE(),
CurrentLevel IN VALUES('Hierarchy'[Targets Level 1]), ReductionTarget1,
CurrentLevel IN VALUES('Hierarchy'[Targets Level 2]), ReductionTarget2,
CurrentLevel IN VALUES('Hierarchy'[Targets Level 3]), ReductionTarget3,
BLANK()
)

 When I use the above measure the reduction target is shown correctly on the inital view (when Fact[Value] is what is seen by the user). But when I drill down to see the hierarchy the target from the first level propogates through.

Any solutions welcome, it may be that making a measure like I have tried to do is the wrong approach!

[v-denglli-msft · Accepted solution]

Hi @JadeM ,

Please try the following measure.

Reduction Target = 
VAR CurrentLevel = 
SWITCH(
TRUE(),
ISINSCOPE('Hierarchy'[Targets Level 3]) = TRUE(), SELECTEDVALUE('Hierarchy'[Targets Level 3]),
ISINSCOPE('Hierarchy'[Targets Level 2]) = TRUE(), SELECTEDVALUE('Hierarchy'[Targets Level 2]),
ISINSCOPE('Hierarchy'[Targets Level 1]) = TRUE(), SELECTEDVALUE('Hierarchy'[Targets Level 1]),
ISINSCOPE('Fact'[Value]) = TRUE(),SELECTEDVALUE('Fact'[Value])
)
VAR ReductionTarget1 = MAX('Fact'[Reduction Target 1])
VAR ReductionTarget2 = MAX('Fact'[Reduction Target 2])
VAR ReductionTarget3 = MAX('Fact'[Reduction Target 3])
RETURN
SWITCH(
TRUE(),
CurrentLevel IN VALUES('Hierarchy'[Targets Level 1]), ReductionTarget1,
CurrentLevel IN VALUES('Hierarchy'[Targets Level 2]), ReductionTarget2,
CurrentLevel IN VALUES('Hierarchy'[Targets Level 3]), ReductionTarget3,
BLANK()
)

The test results are as follows.

The X-axis hierarchy is Value.

X-axis hierarchy drilled down to level 1.

X-axis hierarchy drilled down to level 2.

X-axis hierarchy drilled down to level 3.

Please see the attached pbix for reference.

Best Regards,
Dengliang Li

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