How to remove those row no sales amount using script ?

[admin11]

Hi All

My below script is working fine , it allow me to add in new brand :-

BRAND_N =

IF ( 'SALES'[BRAND_5] = Blank(),

"No Brand",if(SALES[BRAND_5] in

{"HMS","HAKKO","BECKHOFF","EMERSON","MITSUBISHI","MSA","REDLION","UNIVERSAL","GE","PROJECT","NON ","INTERNAL","MOOG","ABB","PARKER","INTERNAL ","INTERNAL ","INTERNAL ","INTERNAL ","ROBOTIQ"},

'SALES'[BRAND_5], "Other"))

My question is how to modify the above script , so that it will smart enough only display those Brand which have sales value.

Paul

[parry2k]

@ALLUREAN doesn't make sense why check for blank() when you are returning blank(), doesn't make sense at all?

Just my 2 cents.

✨ Follow us on LinkedIn

Learn about conditional formatting at Microsoft Reactor

My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[parry2k · Accepted solution]

@admin11 not sure why you are using ISBLANK

Sales Vari % = 
divide(
[Sales YTD]-[Sales LYTD],
[Sales LYTD]
)

✨ Follow us on LinkedIn

Learn about conditional formatting at Microsoft Reactor

My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[ALLUREAN]

In that case you are right and this will ignore empty values, just tried to understand the % measure logic

[admin11]

@parry2k 

Thank you , your script work fine.

@ALLUREAN 

Thank you for your help , after change the sales % vari , it work fine now.

[ALLUREAN]

Hi, @admin11 

One way to achieve this is to set IFISBLANK condition in your % measure, so 100% should dissapear from this table

[admin11]

@ALLUREAN 

My Table have 3 column , YTD LYTD and Vari % see below image :-

[ALLUREAN]

Can you paste the code for Sales Vari %? And if you remove it from the table, is the empty brands also are not showing?

[admin11]

@ALLUREAN 

Yes if i remove the Sales Vari % , the empty row dis-appear.

Below is the Sales Vari % expression :-

Sales Vari % = if(isblank( divide(

[Sales YTD]

-

[Sales LYTD]

,

[Sales LYTD]

)),1, divide(

[Sales YTD]

-

[Sales LYTD]

,

[Sales LYTD]

))

[ALLUREAN]

try replacing 1 by BLANK()

[admin11]

@ALLUREAN 

Thank you for sharing. As my script is a bit complicated , and i am not sure where to insert the condition . Are you able to help me ? Thank you in advance.

[ALLUREAN]

What is your Sales Vari % measure?