Forum Discussion
Helper IMissing Data in inputs resulting in blanks on Matrix
Hello,
For certain warehouses, data does not exist. It isn't 0-- it is non-existent. Thus, changing values from null to 0 doesn't work since it really isn't null- it doesn't exist. As a result, i have a matrix that looks like this....(missing spaces):
I want there to be 0's if it does not exist. I have tried several measures and cant get it to work!
Here's what i want it to look like
Should have mentioned that this is a calculated column. If you must use a measure, add some calculation like MAX() or SELECTEDVALUE(). If you need the totals to work then you need to use an aggregation function like SUMX()
Measure = var a = SUMMARIZE('Table 2','Table 2'[Plant],'Table 2'[Product],"sm", CALCULATE(sum('Table'[Inventory]),TREATAS({([Plant],[Product])},'Table'[Plant],'Table'[Product]))) return 0+sumx(a,[sm])
7 Replies
- lbendlin
Super User
Whitespace reporting (reporting on things that aren't there) requires the use of disconnected tables, crossjoins and special measures.
Please provide sanitized sample data that fully covers your issue. If you paste the data into a table in your post or use one of the file services it will be easier to assist you. Avoid posting screenshots of your source data if possible.
Please show the expected outcome based on the sample data you provided. Screenshots of the expected outcome are ok.
https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523 
ethanlsaulHi There,
It is really a two fold problem-- but i think if i fix this first fold first, it should be easier to fix the missing zeros.
Take a look at the table below and let me know if that makes sense. the stpes are in the top of each table.
Step 1
Raw Data Structure Product Plant Inventory Car 1 US13 2 Car 2 US13 2 Car 6 US13 4 Car 7 US13 3 Car 3 US16 7 Car 4 US16 3 Car 5 US16 5 Car 6 US16 7 Car 8 US19 1 Car 3 US19 10 Car 9 US19 2 Car 7 US19 1 Step 2
Making Plant rows into columns Product US13 US16 US19 Car 1 2 Car 2 2 Car 3 7 10 Car 4 3 Car 5 5 Car 6 4 7 Car 7 1 Car 8 1 Car 9 2
Step 3:
Make columns blanks = 0 Product US13 US16 US19 Car 1 2 0 0 Car 2 2 0 0 Car 3 0 7 10 Car 4 0 3 0 Car 5 0 5 0 Car 6 4 7 0 Car 7 0 0 1 Car 8 0 0 1 Car 9 0 0 2 - ethanlsaul
hey lbendlin -- following up to see if i need to provide you with anything else.
- lbendlin
see attached.
You can further optimize the calculated column like so:
Inventory = 0+CALCULATE(sum('Table'[Inventory]),TREATAS({([Plant],[Product])},'Table'[Plant],'Table'[Product]))- ethanlsaul
i am stuck here. It is odd that your file accepted 'plant' and 'product' but when i do it, it doesn't seem to like it. it seems like it almost only accepts measures and since plant and product aren't measures, it rejects.