Display dynamic 'reduction targets' across multi-level hierarchy in column chart
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!
Blank Values when multiple filters are applied
Hello - I have a simple dataset that looks like this: Category Sub-Category Class Value XX Value YY AAA X1 A 1000 2000 AAA X2 A 400 2500 AAA X3 B 2000 300 BBB X2 A 5000 1000 CCC X1 B 150 1200 CCC X2 B 350 10000 CCC X3 A 300 800 Then I have a summary table that looks like this: Category Value A (=Value XX/2) Value B(=Value YY/2) AAA 1700 2400 BBB 2500 500 CCC 400 6000 Then I have two filters, one for Value A (Filter A) and one for Value B (Filter B), that are the result of the concatenation of: Filter A = "Class" + "Sub-Category" (ie. A-X1, A-X2, B-X3,...) Filter B = "Class_duplicate" + "Sub-Category_duplicate" (ie. A-X1, A-X2, B-X3,...) --> I created a duplicate field for both "Class" and "Sub-Category" to avoid interference between the two filters One of my goals was to apply Filter A only to Value A (with Value B not impacted by any selection) and Filter B to apply only to Value B (with Value A not impacted by any selection). I was able to accomplish this with the following measures: Value A = CALCULATE( DIVIDE(Value XX,2), ALL(Table_name, Class, Sub-Category) ) Value B = CALCULATE( DIVIDE(Value YY,2), ALL(Table_name, Class_duplicate, Sub-Category_duplicate) ) This works only if: I apply one Filter at a time OR if the selected combination of "Class"+"Sub-Category" from both filters is the same But considering that my summary table is at the "Category" level, while my filters are at the "Class"+"Sub-Category" level, the PROBLEM is if: I make a selection from both filters AND the combination of "Class"+"Sub-Category" is different between the two filters, which will return blank values in my summary table for both Value A and Value B. I tried combining different functions inside the measures Value A and Value B, but with no success. I hope I made it clear to understand, any recommendation/hint will be greatly appreciated. Thanks! Davide
