Forum Discussion
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 IF in Scraps of All % measure. it works, but now this 0 is ignoring filter in Machine column
Production:= SUM( Production[ProducedParts] )
Total Scraps:= SUM( Scrap[pocet] )
All:= [Total Production] + [Total Scraps]
Scraps of All %:= DIVIDE( [Scraps], [All] )
Production of All%:= DIVIDE( [Production], [All] )
thank you
12 Replies
You don't need to use IF. Use COALESCE.
Production := COALESCE( SUM( Production[ProducedParts] ), 0 )But you will need to explain what you mean by the machine filter. What is the filter? If you are filtering for blank, then yeah, it won't work when you swap blanks for zeros. Please provide a screenshot if possible of the filter, and how it relates to the data.
Ok, I'm sorry, but I am still not understanding.
"An first printscreen you can see only some of Machines (filter applied at filter pane)" - Yes, but I don't know what that filter is. You provided a screen shot of a filter pane that has no filter selections.
"and all machines at second printscrees (filter is still on) because there is 0 in Scraps of All % measure" - I don't see how your 0 scraps in all % meaure applies to the filter. If that measure returns blank, then by default you may/may not see machines because of how you have the data set. You can see in the image below you can show/hide items with "no data" which means blanks.
" but i need to display only Machines choose in filter" - What are you expecting to see exactly? That is a filter of machines. That is what you should see, only machines. I am not understanding.
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.
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]
Hi Anonymous
If you've fixed the issue on your own please kindly share your solution. if the above posts help, please kindly mark it as a solution to help others find it more quickly. If not, please kindly elaborate more. thanks!
