Conditional Formatting

[e175429]

Hello All,

This is a part of my curent table:

For the "Score Trend", instead of it saying Decline, No change, Improvemement, I would like it to instead show a arrow going up

(for improvement), down (for decline), and a dash ➖ (for no improvement) and then I don't want the words to show either.

[Ray_Minds]

Hi @e175429 
1. First Create data in Power BI using the enter data option in the home tab

2. After that create the calculated column and get the Difference in Sales

3. After that create the sale changes

4. Select the Sale trend > conditional formatting > Icons.

5. Select Rules > Score trend > Icons only. Set the Rules.

6. The result is shown below.

If your requirement is solved, please make THIS ANSWER a SOLUTION ✔️ and help other users find the solution quickly. Please hit the LIKE 👍 button if this comment helps you.

[Anonymous]

Hi @e175429 ,

Based on my testing, please try the following methods:

1.Create the sample table.

2.Create the new measure to calculate score change.

Score change = 
var _last = SUMX('Table', 'Table'[Last Score])
var _current = SUMX('Table', 'Table'[Current Score])
RETURN
DIVIDE(_current - _last, 100)

3.Create the new measure to filter the score trend.

Score trend = 
IF([Score change] > 0, "Improvement", 
    IF([Score change] < 0 , "Decline", "N/A")
)

4.Select the Score trend > conditional formatting > Icons.

5.Select Rules > Score trend > Icons only. Set the Rules.

6.The result is shown below.

Best Regards,

Wisdom Wu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[e175429]

Hello! 

I appreciate your response.

I am getting the following error when calculating the score change:

And I can't calculate the score trend until I score change.

The reason I am getting this error is because there are some people with no scores in the Last Score column. Currently, for those people, it says "N/A". They don't have a "Last Score" but they do have a "Current Score".

I purposely did not write 0 in their "'Last Score" column because I don't want that to be confused with their current score and having "No Improvement". It's not that they didn't improve, they just were not involved the first go around.

Does that make sense? So, as the error says, SUMX cannot work on values with "N/A" in it. And I understand that. But what should I put in place of "N/A"?

[Anonymous · Accepted solution]

Hi @e175429 ,

Based on my testing, please try the following methods:

1.Create the sample table.

2.Create the new column to filter the N/A.

filterlastscore = IF('Table 2'[Last Score] = "N/A", BLANK(), 'Table 2'[Last Score])

3.Create the new column to calculate the score change. Change the column format to percentage.

Score change = DIVIDE('Table 2'[Current Score] - 'Table 2'[filterlastscore], 100)

4.Create the new measure to calculate score trend.

Scoretren = 
IF([Score change] > 0, "Improvement", 
    IF([Score change] < 0 , "Decline", "N/A")
)

Best Regards,

Wisdom Wu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[e175429]

Hey @Anonymous,

I figured it out. I had to combine a IF AND statement.

 Results:

Thank you sooooo much for your help. I would have been stuck forever.

[e175429]

Hi @Anonymous,

Is there a way to factor "No trend" in to the "Trend" column (due to them not participating), because technically they didn't improve(or decline)? For the ones who originally had no score, they shouldn't have a trend.