SUMMARIZE a table with matrix, but show zeroes instead of blanks

[Anonymous]

Hello,

I'm trying to summarize a table, but I need there to be zeroes instead of blanks. Because there is no Aldi - Peanut - G - 2000 data in Sheet1, summarize does not create a zero for this; the same can be said for the other blanks.

Table = SUMMARIZE(Sheet1,Sheet1[Store],Sheet1[Flavor],Sheet1[Brand],Sheet1[Year],"variable","total","count",COUNT(Sheet1[ID]))

ID	Store	Flavor	Brand	Year
1	Kroger	Apple	A	2000
2	Kroger	Apple	A	2000
3	Kroger	Apple	B	2001
4	Kroger	Pear	B	2001
5	Kroger	Pear	C	2002
6	Kroger	Pear	C	2002
7	Walmart	Peach	D	2000
8	Walmart	Peach	D	2000
9	Walmart	Peach	D	2001
10	Walmart	Peach	D	2001
11	Walmart	Peach	D	2002
12	Walmart	Peach	D	2002
13	Walmart	Smokey	E	2001
14	Meijer	Smokey	F	2000
15	Meijer	Smokey	F	2001
16	Meijer	Smokey	F	2001
17	Meijer	Cherry	F	2002
18	Meijer	Cherry	G	2002
19	Aldi	Peanut	G	2001
20	Aldi	Peanut	G	2002

[Anonymous · Accepted solution]

To solve this problem, I used both @ryan_mayu and @AilleryO solutions. My summarize function will work fine, but after that, you must create a "Year" table like ryan_mayu advises, then you must create a measure that adds 0 as AilleryO advises. For my case, that would be:

Measure = CALCUALTE(SUM('Table'[count]))+0

Thank you both for the help.

[AilleryO]

Hi,

Did you try to add 0 to your measure ?

MyMeasure = SUM( MyColumn) +0

Let us know.

[v-priyankata]

Hi @Anonymous 

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[ryan_mayu]

@Anonymous 

you can create a new table 

Table 2 = DISTINCT('Table'[Year])

create relationship between two tables.

then add the year column from new table to the column field

[Anonymous]

This is a great solution if I do not use SUMMARIZE, but I unfortunately need to use SUMMARIZE for this because I'm going to union summarize tables to show different "variables" (column in my original post).

[ryan_mayu]

sry don't know why need to use summarzie function. What if you connect the new table to your summarized table?