Looker/Google Data Studio - blending two tables (missing values, also null)

Question

1. DATA I have a following source data in Google Sheets:

CALENDAR_FULL_DATE	MATERIAL_GROUP	MATERIAL	STORAGE_LOCATION	CATEGORY	QUANTITY
1 September 2022	AAAAA	11111	121	CONSUMPTION	64
1 September 2022	AAAAA	11111	122	CONSUMPTION	1
1 September 2022	AAAAA	33333	124	CORRECTION	66
1 September 2021	AAAAA	33333	152	CORRECTION	29
...	...	...	...	...	...

2. EXPECTED RESULT In Looker/Google Data Studio I would like to create a following pivot table (and then play around with graphs, heat maps etc):

MATERIAL_GROUP	MATERIAL	CONSUMPTION	CORRECTION	RATIO
AAAAA	11111	65		0%
AAAAA	33333	628	341	54%
AAAAA	44444	29	134	462%
AAAAA	55555		86	0%
AAAAA	66666		34	0%
AAAAA	77777		121	0%
BBBBB	99999	234	339	145%

3) Chart: Configuration + Setup In my dashboard you can see that I created two tables (one with category = correction and another one with category = consumption). Then I blended and created a calculated field to obtain a ratio.

4) Issue: Attempt at solving + Output

a) I am missing some SKUs (understandably, because there is no 2-way-match in the blend for some SKUs, e.g. SKU 55555)

b) When calculating errors, I would like to see "0" instead of "null" (e.g. SKU 11111). Similarly, if there is an error (dividing by non-existing number, e.g. SKU 55555"), I would also like to see "0".

Answer 1

Added Solution in page 2 of your dashboard

trick is to use COALESCE function in both the dimensions MATERIAL_GROUP and MATERIAL