Add Missing items with null/blank/0

[Anonymous]

Hi Community,

I have a table with 3 columns, Name is slicer in my report. If i select Bala it will show all 4 region details of bala.

If i select Dhanpal or Sudhakar it needs to show all 4 regions for both with blank/Null,0 value in sales column.

This is my Table:

Name	Region	Sale
Bala	East	10
Bala	West	20
Bala	North	30
Bala	South	40
Dhanpal	East	25
Dhanpal	West	35
Dhanpal	South	45
Sudhakar	West	15
Sudhakar	North	40

My Desired Output: If i select Sudhakar

Name	Region	Sale
Sudhakar	West	15
Sudhakar	North	40
Sudhakar	East	Null
Sudhakar	South	Null

Can you guys please help me out from this

Thank you in advance

B V S S

[Anonymous]

Hi @Anonymous,

I think you need to create a calculated table with all region values and use it on your visual with the raw table category.  Then you can write a measure formula to lookup value based on current category and region, use the if statement to replace the not matched value to blank.

Regards,

Xiaoxin Sheng

[amitchandak · Accepted solution]

@Anonymous , You have create a measure with +0. Also change data type sales to number

Please find the file attached after signature. Check for Measure 2

[amitchandak]

@Anonymous , As Sudhakar do not have all the values.

You need have Region dimesion as separate table joined to this table with region and then use +0 to the measure or use this option - Show  Item with no Data for Region

[Anonymous]

Hi @amitchandak ,

Thank you for the response.

I tried your solution. But no luck. 

Here Download here i am attaching my pbix file. can you please have a look and let me know what i am missing

Thank you in advance

B V S S