cancel
Showing results for
Did you mean:
Regular Visitor

## Dynamic Scorecard logic

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

3 REPLIES 3
Senior Member

## Re: Dynamic Scorecard logic

What do you mean by reflecting in this case?

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

## 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.

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