DAX: ConditionalFormatting on Percentages in Matrix doesn't react

[Titatovenaar2]

Hi everyone,

I try to color all percentages of measure <pB&B_B1> (renamed in column as 'B1') red between 0 and 10%, and orange between 10 and 30%.

EDIT:

The following all doesn't work:

0 - 0.1 and 0.1 - 0.3 w/ numbers

0 - 10 and 10 - 30 w/ numbers (obviously, but had to try to be sure)

0 - 0.1 and 0.1 - 0.3 w/ percentages (obviously, but had to try to be sure)

0 - 10 and 10 - 30 w/ percentages

Somehow the following by using numbers doesn't work:

And also by using percentages, it does not react:

When I take a measure to check why the conditional formatting doesn't react, it seems to make sense, because the following measure only yields '0' for all records:

pB&B_ConditionalFormattingTest
IF(AND([pB&B_B1] > 0, [pB&B_B1] < 0.1), 1, 0)

With the following measure every records turns into a '1', including the entirely blanks.

pB&B_ConditionalFormattingTest
IF([pB&B_B1] > 0, 1, 0)

But somehow when I try the following measure, it still only results into a 0 per record. which I find weird, since I took an unreasonably high number:

pB&B_Testconditionalformat = 
IF([pB&B_B1] < 9999999999999999999999999999999999999999, 1, 0)

As you can see, this is what it gives:

The pB&B_B1 measure does the following:

pB&B_B1 = 
VAR totaleBesteding = CALCULATE([Omzet],Artikel[Artikel categorie code] IN {"B1","B2","B3","B4","B5","B6"})
VAR artikelCategorie = CALCULATE([Omzet],Artikel[Artikel categorie code]  = "B1")
Return
FORMAT(artikelCategorie / totaleBesteding, "Percent")

While the measure within the one above, does nothing more than the following:

Omzet = 
CALCULATE(SUM('Fact'[Bedrag]),
'Fact'[Source] IN {"GeleverdeOrders","TOTAAL_REGELS","TOESLAGEN"}
)

It's a simple star schema, so I don't understand why it behaves the way it does and how to make the conditional formatting to work properly. Any suggestions are greatly appreciated!

Kind regards,

Igor

[Titatovenaar2 · Accepted solution]

I found the solution, I didn't realize that FORMAT() function, even though setting it to a '%' via "percentage", it also changes it's datatype to String. Therefore it returned a 0 even while I compared it with an insanely high number of 99999999999999999-ish. Once I removed the FORMAT() function, and changed the datatype of the measure directly to '%', the 0 - 0.1 and 0.1 - 0.3 as numbers works perfect!

[amitchandak]

@Titatovenaar2 , Percent column if marked as % the values are between 0 and 1 where 1 is displayed as 100, and so we should use .2 for 20% .5 for 50% and use number.

Percent here means distribution of data between min and max values

[Titatovenaar2]

As you can see I mention that as an example too. It does not work in my situation apparently.

I have tried:

0 - 0.1 and 0.1 - 0.3 as number, without success

0 - 10 and 10 - 30 as percentage without success

0 - 0.1 and 0.1 - 0.3 as percentage, also without success

That's why I made a test measure to check whether it gives a 0 or 1, and that already clearly shows weird results.