Dynamic Scorecard logic

[JS]

Hello all experts. 

I will need some help. 

I have a simple data below 

Country	Team	Actual	Plan
London	Team A	120	105
London	Team B	95	80
London	Team C	87	100
France	Team A	110	150
France	Team B	100	80
France	Team C	150	110

I want to reflect teams that are above plan on a score card. For instance, if the slicer that I have selected is "London",

I will therefore have the following scorecard reflecting - 

Above Plan - Team A, Team B

Below Plan - Team C

I am able to achieve the above. However, it becomes tricky when I select both London & France. 

I need to have the following output - 

Above Plan - Team A, Team B, Team C.

Below Plan - (Blank)

Any help will be grateful on playing around with the possible DAX to get the output! Thanks! 

JS

[Anonymous]

HI @JS,

I'd like to suggest refer to below steps to achieve your requirement:

1. Add calculated column to check category.

Categroy = 
IF (
    [Actual] > [Plan],
    "Above Plan",
    IF ( [Actual] < [Plan], "Below Plan", "Achieve Plan" )
)

2. Write measure to display matched team group.

Result = 
CONCATENATEX ( VALUES ( Table1[Team] ), [Team], "," )

Result without Group = 
VAR list =
    CALCULATETABLE ( VALUES ( Table1[Team] ), VALUES ( Table1[Categroy] ) )
VAR excepted =
    EXCEPT (
        DISTINCT ( ALLSELECTED ( Table1[Team] ) ),
        CALCULATETABLE ( VALUES ( Table1[Team] ), Table1[Categroy] <> "Below Plan" )
    )
RETURN
    IF (
        SELECTEDVALUE ( Table1[Categroy] ) <> "Below Plan",
        CONCATENATEX ( list, [Team], "," ),
        IF (
            COUNTROWS ( excepted ) > 0,
            CONCATENATEX ( excepted, [Team], "," ),
            "null"
        )
    )

3. Cretae visuals.

Regards,

Xiaoxin Sheng

[Thejeswar]

What do you mean by reflecting in this case?

[JS]

Hi Thejeswar

What I meant is the scorecard will reflect the appropriate team that is above or below plan.