how to replace blank fields to zero in matrix visual

[IlseV]

Hi I have a table

Itemnumber	Date	Value
A	12/09/2022	20
A	13/09/2022	50
A	14/09/2022	60
B	13/09/2022	40
B	14/09/2022	10
C	12/09/2022	70
C	14/09/2022	40
D	13/09/2022	50

and I want the blank fields to be filled up with zero, so I can perform calculations, but I only get blanks instead of zero

Itemnumber	13/09/2022	14/09/2022
A	50	60
B	40	10
C	0	40
D	50	0

[tamerj1]

Hi @IlseV 
You need to have independent dimention tables as follows. Please refr to atached sample file

 

Measure = SUM ( FactTable[Value] ) + 0

[IlseV]

https://drive.google.com/file/d/12pYfxuAAOB_bL65QkX5RhVZSkEaA4PYU/view?usp=sharing

[Jihwan_Kim · Accepted solution]

Hi,

thank you for sharing.

In my opinion, data model has to be created by using item dimension table and date dimension table.

Please check the attached pbix file and the below picture.

[IlseV]

Wow, this works. Many thanks!

I now added a Difference - measure to get a view on the difference in numbers between 2 days.

I got the tip from another post:

Difference = Var vPreviousDay = CALCULATE(Sheet1[expected measure:], FILTER(Sheet1,Sheet1[Days since]=MIN(Sheet1[Days since])))

Var vPreviousDay1Back = CALCULATE(Sheet1[expected measure:], FILTER(Sheet1,Sheet1[Days since]=MIN(Sheet1[Days since])+1))

RETURN vPreviousDay-vPreviousDay1Back

But in the final matrix, I would in the lowest row expect -50 instead of +50, because I substract 0 minus 50.

Do you have any advice on this matter? I add again the link to the pbix.

https://drive.google.com/file/d/1o7RTfcFyD0DiYLZkZAa4gN17T-i0Equc/view?usp=sharing

Many thanks.

[Jihwan_Kim]

Hi,

One of ways to achieve this is to create a measure something like below, and put it into the matrix visualization.

New measure: = SUM(Table[Value]) + 0

[IlseV]

Hi, I already tried this, but it doesn't work. The blank fields still remain blank.

[Jihwan_Kim]

Hi,

Thank you for your feedback, and please share your sample pbix file's link here, and then I can try to look into it to come up with a more accurate solution.

Thanks.

[IlseV]

Hi, I can't add pbix files here

[Jihwan_Kim]

Hi,

please try to share Onedrive link, Googledrive link, Dropbox link, or any other link that allows to share the file, instead of attaching the file to here.

Thanks.