Help with aggregated target performance measures, blank values and incorrect results

[JadeM]

Hi all,

I have a problem with making a summary table for a power BI report I have.

I want to make a summary table that looks similar to this:

Target Description	Target	Performance
Reduce fossil fuels	-50%	-44%
Reduce heating	-40%	-43%
Reduce paper	-10%	-20%
Reduce waste	-60%	-50%
Recycle 80% of waste	80%	55%
Reduce landfill to 5% of waste	5%	15%
50% of cars to be electric	50%	49%

The issue is that the types of targets that need to be in this table are different. For the first four, it is a simple reduction from year one. For this I have a measure [Performance] which works correctly in all charts.

For the recycling and landfill targets I have had to calculate what percentage recycling/landfill is of all waste, I have used this measure (landfill measure has the same set up):

Recycling =
VAR Recycling = CALCULATE(SUM('Fact'[Actual], 'Fact'[Value] = "Total recycling"
VAR TotalWaste = CALCULATE(SUM('Fact'[Actual], 'Fact'[Value] IN {"Total recycling", "Total landfill", "Total incinerated"})

RETURN
IF(TotalWaste > 0, Recycling / TotalWaste, BLANK())

For the car target I have had to calculate the % based on all types of cars, as below:

eCars =
VAR eCar = CALCULATE(SUM('Fact'[Actual], 'Fact'[Sub Target] IN {"Small electric car", "Large electric car"}
VAR TotalCars = CALCULATE(SUM('Fact'[Actual], 'Fact'[Sub Target] IN {"Small electric car", "Large electric car", "Small petrol car", "Large electric car", "Small diesel car"}

RETURN
IF(TotalCars > 0, eCar / TotalCars, BLANK()

So to combine all my measure to show performance in one column in my table (as above) I have made this measure:

Overall Performance =
VAR TargetName = SELECTEDVALUE('Overview'[Target Description])
VAR eCar = [eCar]
VAR Recycling = [Recycling]
VAR Landfill = [Landfill]
VAR OtherTarget = [Performance]

RETURN
SWITCH(
TRUE(),
TargetName = "50% of cars to be electric", eCar,
TargetName = "Recycle 80% of waste", Recycling,
TargetName = "Reduce landfill to 5% of waste", Landfill,
OtherTarget)

The issue I am having is that in the matrix the values of the cars, landfill and recycling rows are blank.

I think this is because of a lack of relationship! But I cannot figure out how to make a relationship given the fact that these targets are calculated using numerous rows!

Here is a basic view of the model:

Many to one relationship on the Fact[Value] and Overview[Short target] columns.

Note! There is not a matching value in Fact[Value] for the cars, landfill and recycling targets as they are calculated using many columns (eg small electric car, large electric car etc)

Here is sample data:

Fact:

Value	Sub Target	Actual	Baseline
Heating	Electricty	400	1000
Heating	Gas	500	900
Electric car	Small electric car	100	20
Electric Car	Large electric car	45	2
Car	Small petrol car	55	50
Car	Large petrol car	660	900
Waste	Total recycling	450	300
Waste	Total landfill	50	99
Waste	Total incinerated	110	25

Then the overview table:

Short target	Target description	Target
GHG	Reduce fossil fuels	-50%
Heating	Reduce heating	-40%
Paper	Reduce paper	-10%
Waste	Reduce waste	-60%
Recycling	Recycle 80% of waste	80%
Landfill	Reduce landfill to 5% of waste	5%
Cars	50% of cars to be electric	50%

So you can see that there is not a matching value for recycling, landfill and cars in the 'Fact' table. I believe this is causing the blank value - as PBI cannot match everythign together.

Is there any way of making the table in the design I would like? adding a new column, changing the Overall Performance measure? any help appriciated. Thanks!

[Anonymous · Accepted solution]

Hi   @JadeM 

Yes, the lack of matching values is indeed the cause of blanks. To solve this problem, there are two ways.

The first is to add a new column to the Fact table to group categories without matching values into matching value categories, such as classifying small electric car, large electric car as Cars. In this way, the values in the new column and the [Short target] values in the Overview table can exactly match, and then the relationship is based on the new column and the [Short target] column. For example, 

Another way is to modify the measures. Remove the filtering of existing relationship from the measures, and then filter the matching values by expressions. For example, 

Recycling = 
VAR Recycling = CALCULATE(SUM('Fact'[Actual]), REMOVEFILTERS('Fact'[Value]), 'Fact'[Value] = "Total recycling" )
VAR TotalWaste = CALCULATE(SUM('Fact'[Actual]), REMOVEFILTERS('Fact'[Value]), 'Fact'[Value] IN {"Total recycling", "Total landfill", "Total incinerated"}) 
RETURN 
IF(TotalWaste > 0, Recycling / TotalWaste, BLANK())

Modify [Recycling], [Landfill] and [eCar] measures similarly. 

Hope this would be helpful. 

Best Regards,
Jing
If this post helps, please Accept it as Solution to help other members find it. Appreciate your Kudos!