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

Dynamic Scorecard logic

Hello all experts. 

 

I will need some help. 

 

I have a simple data below 

 

CountryTeamActualPlan
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! 

 

JS

3 REPLIES 3
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

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.

32.gif

 

Regards,

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: | |