Showing results for 
Search instead for 
Did you mean: 
JS Regular Visitor
Regular Visitor

Dynamic Scorecard logic

Hello all experts. 


I will need some help. 


I have a simple data below 


LondonTeam A120105
London Team B9580
London Team C87100
FranceTeam A110150
FranceTeam B10080
FranceTeam C150110



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! 



Thejeswar Senior Member
Senior Member

Re: Dynamic Scorecard logic

What do you mean by reflecting in this case?

JS Regular Visitor
Regular Visitor

Re: Dynamic Scorecard logic

Hi Thejeswar

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

Re: Dynamic Scorecard logic



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" )
    IF (
        SELECTEDVALUE ( Table1[Categroy] ) <> "Below Plan",
        CONCATENATEX ( list, [Team], "," ),
        IF (
            COUNTROWS ( excepted ) > 0,
            CONCATENATEX ( excepted, [Team], "," ),

3. Cretae visuals.




Xiaoxin Sheng

Community Support Team _ Xiaoxin Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

For learning resources/Release notes, please visit: | |