Grouping of scores per region

[Anonymous]

Hi,

I am working on an a requirement to build a KPI  condition where for the KPI to be met it needs to fullfill 2 conditions.

Both conditions below should be met for the KPI to be considered as met else its not met. 

1. No country should be less than 90%.

2. No Region should be less than 95%.

Which essentially means that even though the region meets its target however if any country misses its target than the KPI is not met.
I would like to know how do i build this logic/calculation where

1. I can group the scores by country and region.

2.  Build the KPI condition mentioned above.

Here is a sample data 
NOTE: The way region is repeating in this data in the real data country also repeats.

Region	Country	Score
North America	United States	100%
North America	Canada	90%
Latin America	Mexico	100%
Latin America	Trinidad and Tobago	98%
Latin America	Bolivia	85%

Shariq

[amitchandak]

@Anonymous , see if this can help

https://www.daxpatterns.com/dynamic-segmentation/
https://www.daxpatterns.com/static-segmentation/
https://www.poweredsolutions.co/2020/01/11/dax-vs-power-query-static-segmentation-in-power-bi-dax-power-query/
https://radacad.com/grouping-and-binning-step-towards-better-data-visualization

[mahoneypat]

Assuming that the Region score is the average of the Country scores (you can change it if not), here is an expression that should work for your KPI.

Met Goals =
VAR __regionmin =
    MINX ( VALUES ( Scores[Region] ), CALCULATE ( AVERAGE ( Scores[Score] ) ) )
VAR __countrymin =
    MINX ( VALUES ( Scores[Country] ), CALCULATE ( AVERAGE ( Scores[Score] ) ) )
RETURN
    IF ( AND ( __regionmin >= 0.95, __countrymin >= 0.9 ), "Yes - 😊", "No - 😢" )

You said this was a KPI, so I used emoticons in the output (using Windows Key + "."), so please change to what you want to show.  Here is what the output might look like on a report page with slicers for Region and Country.

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[Anonymous]

@mahoneypat the average for region needs to be the average of only the countries in that region. So i am thinking the filter you are applying via slicer needs to be applied within the calculation.

[mahoneypat]

@Anonymous , the CALCULATE() in the region part ensures that only the countries in that region are included in the calculation for that region.  Please let me know the scenario in which you see an incorrect result.  Since it does MINX over regions and countries, it should work whether Region or Country is selected or not.  Only question is if a single Country is selected, how do you want that Region calculated (just use the selected country and calculate region result (current case), or remove country filter and include all countries for that region).

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[parry2k]

@Anonymous how the region scores will be calculated, is that going to be average of all the countries under the region?

[Anonymous]

@parry2k yes exactly.