Missing Data in inputs resulting in blanks on Matrix

[ethanlsaul]

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 

[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.

[lbendlin · Accepted solution]

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])

[ethanlsaul]

IT WORKED!!! Thank you so much. 

[ethanlsaul]

Hi 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

[lbendlin]

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