Forum Discussion
Add Missing items with null/blank/0
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 , 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
4 Replies
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
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
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
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