Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Repleacing blank() with zeros

I have a table with simple SUM measures, but i need to replace blanks with zeros. If I change measure with IF statement , it will remove filter applied to Machine column   withou IF   with ...
Need Help
jdbuchanan71's avatar
jdbuchanan71
Super User
6 years ago

Anonymous 

You can use a trick of DIVIDE to exclude the rows where there is no [Production] and then you can use the IF like so:

Scrap of All % = DIVIDE([Production],[Production]) * IF ( [Scraps] = 0, 0, DIVIDE ( [Scraps], [All], 0 ) )

DIVIDE([Production],[Production]) returns a BLANK() if there is not [Prodution] and a 1 if there is [Prodcution].  BLANK * a value = BLANK so then the IF only shows on lines where there is production and the % is 0 when there is no [Scraps] 

you can see in my data I have more machines but some have to [Production].  With just the IF I get the 0 on rows that should be empty but the corrected measure from above give me a blank on those rows so they will drop out when I am only showing the good measure.

jdbuchanan71's avatar
jdbuchanan71
Super User
6 years ago

We can make it a bit better and avoid calcing measures multiple times using a VAR.

 

Scrap of All % =
VAR _All = [All]
VAR _Scraps = [Scraps]
RETURN 
    DIVIDE ( _All, _All ) * IF ( _Scraps = 0, 0, DIVIDE ( _Scraps, _All, 0 ) )

 

 I also changed it to check the [All] rather than [Prodution]