Forum Discussion

JS's avatar
JS
Icon for Helper II rankHelper II
8 years ago

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

Need Help

3 Replies

    • JS's avatar
      JS
      Icon for Helper II rankHelper II
      8 years ago
      Hi Thejeswar

      What I meant is the scorecard will reflect the appropriate team that is above or below plan.
  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    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