Introduced Colour and Text Comment for Infinity & NaN

[Anonymous]

Hi,

I want to know if I can make the  cells red for the remaining Infinity values? It worked when I used the background colour for percentage above 1 but not all of them get the red cell. No success for NaN wherein I want to have yellow cells

Another question...how can I make a dax for Infinity & NaN to show Text "No PO With Cost" and "No Cost, No PO" respectively?

[dharmendars007]

Hello @Anonymous , 

Create a new measure to determine the formatting rules like below

Format Measure =
SWITCH(
TRUE(),
ISBLANK([Percentage]), 2, -- Yellow for NaN
NOT ISNUMBER([Percentage]), 3, -- Red for Infinity
1 -- Default (No Formatting))

2 corresponds to yellow (NaN).
3 corresponds to red (Infinity).
Default formatting applies otherwise.

You can also use a calculated column or measure in DAX to replace Infinity and NaN with your desired text like below.

Custom Percentage =
SWITCH(
TRUE(),
ISBLANK([Percentage]), "No Cost, No PO", -- NaN
NOT ISNUMBER([Percentage]), "No PO With Cost", -- Infinity
FORMAT([Percentage], "0.00%") -- Default percentage format)

If you find this helpful , please mark it as solution which will be helpful for others and Your Kudos/Likes 👍 are much appreciated!

Thank You

Dharmendar S

LinkedIN 

[Anonymous]

Hi,

Here are the results...

The required texts are not being captured...

Thanks...tksnota...