Replacing Blanks with Zeros in a Matrix

[AN3]

Hi Everyone,

I have tried multiple measures and DAX formulas suggested online in this community and not one of them is working for me.

I am building a location plan by Headcount for my organisation and I have build a matrix table to show this data, however there are certain BSLs that have no headcount for a particular month in the particular site.  As I have conditional formatting set up based on a calculated field.  I need the blanks to show up as a Zero so the conditional formatting will work.

I have tried multiple different measures but when I add them into the table it just ends up duplicating the data with a bunch of zeros and throwing the order out of whack.

Sample Data:

Group	BSL	State	Site	HC
A	Aa	ACT	NAT	1
A	Aa	NSW	ALB	1
A	Aa	NSW	NEW	1
A	Aa	NSW	PAR	1
A	Aa	NSW	PEN	1
A	Aa	NSW	SYD	1
A	Aa	NSW	WOL	1
A	Aa	QLD	BRI	1
A	Aa	QLD	TOW	1
A	Aa	QLD	UMG	1
A	Aa	SA	ADE	1
A	Aa	TAS	HOB	1
A	Aa	VIC	BOX	1
A	Aa	VIC	DAN	1
A	Aa	VIC	MEL	1
A	Aa	VIC	MPO	1
A	Aa	WA	PER	1
A	Ab	ACT	NAT	1
A	Ab	NSW	ALB	1
A	Ab	NSW	GOS	1
A	Ab	NSW	NEW	1
A	Ab	NSW	PAR	1
A	Ab	NSW	PEN	1
A	Ab	NSW	SYD	1
A	Ab	NSW	WOL	1
A	Ab	QLD	BRI	1
A	Ab	QLD	TOW	1
A	Ab	QLD	UMG	1
A	Ab	SA	ADE	1
A	Ab	TAS	HOB	1
A	Ab	VIC	BOX	1
A	Ab	VIC	DAN	1
A	Ab	VIC	MEL	1
A	Ab	VIC	MPO	1
A	Ab	WA	PER	1
A	Ac	ACT	NAT	1
A	Ac	NSW	ALB	1
A	Ac	NSW	NEW	1
A	Ac	NSW	PAR	1
A	Ac	NSW	PEN	1
A	Ac	NSW	SYD	1
A	Ac	NSW	WOL	1
A	Ac	QLD	BRI	1
A	Ac	QLD	TOW	1
A	Ac	QLD	UMG	1
A	Ac	SA	ADE	1
A	Ac	TAS	HOB	1
A	Ac	VIC	BOX	1
A	Ac	VIC	DAN	1
A	Ac	VIC	GEE	1
A	Ac	VIC	MEL	1
A	Ac	VIC	MPO	1
A	Ac	WA	PER	1
A	Ad	ACT	NAT	1
A	Ad	NSW	NEW	1
A	Ad	NSW	PAR	1
A	Ad	NSW	SYD	1
A	Ad	QLD	BRI	1
A	Ad	QLD	TOW	1
A	Ad	QLD	UMG	1
A	Ad	SA	ADE	1
A	Ad	TAS	HOB	1
A	Ad	VIC	BOX	1
A	Ad	VIC	DAN	1
A	Ad	VIC	MEL	1
A	Ad	VIC	MPO	1
A	Ad	VIC	TRA	1
A	Ad	WA	PER	1
A	Ae	ACT	NAT	1
A	Ae	NSW	ALB	1
A	Ae	NSW	GOS	1
A	Ae	NSW	NEW	1
A	Ae	NSW	PAR	1
A	Ae	NSW	PEN	1
A	Ae	NSW	SYD	1
A	Ae	NSW	WOL	1
A	Ae	QLD	BRI	1
A	Ae	QLD	TOW	1
A	Ae	QLD	UMG	1
A	Ae	SA	ADE	1
A	Ae	TAS	HOB	1
A	Ae	VIC	BOX	1
A	Ae	VIC	DAN	1
A	Ae	VIC	GEE	1
A	Ae	VIC	MEL	1
A	Ae	VIC	MPO	1
A	Ae	WA	PER	1
A	Af	ACT	NAT	1
A	Af	NSW	ALB	1
A	Af	NSW	NEW	1
A	Af	NSW	PAR	1
A	Af	NSW	SYD	1
A	Af	NSW	WOL	1
A	Af	QLD	BRI	1
A	Af	QLD	UMG	1
A	Af	SA	ADE	1
A	Af	TAS	HOB	1
A	Af	VIC	BOX	1
A	Af	VIC	DAN	1
A	Af	VIC	MEL	1
A	Af	VIC	MPO	1
A	Af	WA	PER	1
A	Ag	ACT	NAT	1
A	Ag	NSW	ALB	1
A	Ag	NSW	GOS	1
A	Ag	NSW	NEW	1
A	Ag	NSW	PAR	1
A	Ag	NSW	PEN	1
A	Ag	NSW	SYD	1
A	Ag	NSW	WOL	1
A	Ag	QLD	BRI	1
A	Ag	QLD	TOW	1
A	Ag	QLD	UMG	1
A	Ag	SA	ADE	1
A	Ag	TAS	HOB	1
A	Ag	VIC	BOX	1
A	Ag	VIC	DAN	1
A	Ag	VIC	GEE	1
A	Ag	VIC	MEL	1
A	Ag	VIC	MPO	1
A	Ag	WA	PER	1
A	Ah	ACT	NAT	1
A	Ah	NSW	ALB	1
A	Ah	NSW	GOS	1
A	Ah	NSW	NEW	1
A	Ah	NSW	PAR	1
A	Ah	NSW	PEN	1
A	Ah	NSW	SYD	1
A	Ah	NSW	WOL	1
A	Ah	QLD	BRI	1
A	Ah	QLD	TOW	1
A	Ah	QLD	UMG	1
A	Ah	SA	ADE	1
A	Ah	TAS	BUR	1
A	Ah	TAS	HOB	1
A	Ah	VIC	BOX	1
A	Ah	VIC	DAN	1
A	Ah	VIC	GEE	1
A	Ah	VIC	MEL	1
A	Ah	VIC	MPO	1
A	Ah	WA	PER	1

The picture above highlights the yellow spaces that are currently blank, I want these to show up in Power BI as Zero.

Measure that I have tried include:

Headcount (Blank replaced) = IF(ISBLANK(SUMX(SSOT,[Headcount])), 0 , SUMX(SSOT,[Headcount]))

All Blank Rows =

[Head Count] = BLANK()

Replace HC = IF([All Blank Rows], BLANK(), COALESCE([Head Count],0))

Headcount = sum(Table[Headcount])+0

Headcount = IF('Table'[Col_1]=BLANK(),0,'Table'[Col_1])

With all these measures all I get is this:

 

My filters on this matrix include - Group, Site and Date

My relationships behind the tables are like this:

 If anyone could help, I would really appreciate it.

Thank you,

[v-zhangti · Accepted solution]

Hi, @AN3 

You can try the following methods.
New Table

Table 2 = SUMMARIZE('Table','Table'[Group],'Table'[BSL])

Measure = SUM('Table'[HC])+0

Is this the result you expect?

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[AN3]

Hi,

Thanks for your help, this solution worked, except that I had to summarize both the Group and BSL as a Table and the Site and the State as a separate Table, deleting duplicates that were affecting the relationship.  Once I connected both these tables up, I could then use the calculation measure and it worked.  

Thank you very much for your help 🙂