Percentage Target KPI show unique value

[Anonymous]

Hi all, 

I have an issue with showing targets in the correct way. We have targets for managerial divisions, entities and countries within these divisions and entities. Furthermore, in our data model we have one master sheet defining which countries are within the entities and which entities are within each division. In that way we can have one slicer that defines this structure, so we can slice on multiple measures across tables. However, for our target table we have an issue that we can't make a target measure that doesn't sum up the KPI's for all entities within a division if we want to see our target on a divisional level. For instance when showing a Cluster like APAC, the Dashboard should show the ASP for that cluster, but instead it summarizes all ASP's for each country within the Cluster. Is there any way to solve this? 

Following is an example:

The entity master file:

Managerial Entity	Reporting Entity
ANZ	Australia
ANZ	New Zealand

The KPI Target Table

KPI	Managerial Entity	Reporting Entity	Value
ASP	ANZ		200
ASP		Australia	150
ASP		New Zealand	250

When I then do the following Formula :

Calculate(sum('KPI Targets'[Value]);'KPI Targets'[KPI]="ASP")

Then I set the Slicer based on the Entity Master file to ANZ and get the following:

ASP Target = 400 ( 150+250). Because the Entity Master File overrides the KPI Target file and sums Australia+New Zealand

The correct answer should be ASP Target = 200. 

Thank you very much in advance. 

[v-kelly-msft · Accepted solution]

Hi @Anonymous ，

First create a calculated column as below:

Column = IF('Table (2)'[Managerial Entity]=BLANK(),LOOKUPVALUE('Table'[Managerial Entity],'Table'[Reporting Entity],'Table (2)'[Reporting Entity]),'Table (2)'[Managerial Entity])

Then create a measure as below:

Target = CALCULATE(SUM('Table (2)'[Value]),FILTER('Table (2)','Table (2)'[Reporting Entity]=BLANK()&&'Table (2)'[Column]=SELECTEDVALUE('Table (2)'[Column])))

Finally you will see:

For the related .pbix file, pls click here.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[Anonymous]

This is amazing, thank you very much! 

[Greg_Deckler]

Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

[Anonymous]

Hi Greg, 

Thank you for your message. I have now changed my issue - hope it makes sense now.