M Code Percentage Thresholds

[smather]

Hi

I created a custom column in the Query Editor: 

if [Phase] = "Equipment" and [Margin] < 20 then "Below Threshold" else null

I've also used 0.20/0.19 and 19 to no avail, as it keeps highlighting things as 'below theshold' when they're not, so there's something wrong with my code 😞 I do have the cost/sell and margin £ on this table as well, would it be worth doing the formula to figure out the percentage rather than refering to the Margin % col? If it makes any difference, the margin col pulls through as ie 30.00%

Any help would be grand 🙂 

[v-janeyg-msft · Accepted solution]

Hi, @smather 

You can try: if text.trim([Phase]) = "Equipment" and [Margin] < 20 then "Below Threshold" else null

If it doesn’t solve your problem, please feel free to ask me.

Best Regards

Janey Guo

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

[smather]

@PhilipTreacy 

The file is giant and connected to our server, unfort I can't share it. Apologies about the screenshots, lemme try again. I can confirm that the margin is down as % and there's no spaces in any of the phase names (or, there isn't at the source).  

I'll give an example of my data (and ideally what it should spit out). Each line is a item, be it labour, kit or licences. And at the visual level, it aggreates them together based off the phase. 

Here's some screenshots (I made my own margin box, just in case the one pulling through was playing silly):  

I just don't understand. All those are for the same project, each line is just another labour day. 

Sorry about the new thread, the forum would not let me post a reply to you 😞

[PhilipTreacy]

Thx @smather 

Well without the actual data to hand I think I've suggested everything I'd check.

If % Margin is 30% as shown in your images then the only thing I can think of is that there are some non-prining characters or spaces in the Phase col, but you said that's not the case.

Can you please humour me and double check with this new column

 = Text.Length([Phase])

Regards

Phil

[smather]

I think I've figured it! I changed the formula to say <=0.29 rather than <0.30 and it seems to be spitting out what I expected

[smather]

Hey @PhilipTreacy I was kind of hoping it'd spit out different character lengths because at least thenI'd have a fix

any other suggestions? I'm game to try anything at this point 🙂 

[Carmichael]

Can you share a screen shot of the data with the data types visible?

I recreated based on my underrsanding and your formula worked for me.

= Table.AddColumn(#"Changed Type", "AND", each if [Phase] = "Equipment" and [Margin] < 0.2 then "Below" else null)

Result

[smather]

Can do! See below: 

There's no spaces in the phase col - the data source doesn't have any, so unless BI is off doing its own thing (stranger things have happened!), that shouldn't be the issue 😕

[PhilipTreacy]

Hi @smather 

The screenshots are a bit confusing.  Some of it is PQ but doesn't show the custom column, some is a Table viz?

Can you actually share some real data we can test?  Or share the PBIX?

Regards

Phil

[PhilipTreacy]

Hi @smather 

Does the Phase column have text with leading or trailing spaces e.g. " Equipment " ?

Is the Margin column definitely numeric?  It's not numbers stored as text?

If you supply some real sample data it would help to be able to check it.

Regards

Phil

[amitchandak]

@smather , first of check the datatype and some values. Because ideally .2 or 20 one or the value should work

Also, check case and space issue with value Equipment in column Phase