Filling missing value with 0 in pivot table

Question & Answer
,
1

I have a dataset which contains Month, Scheme Name, Broker id and AUM. I need to create a pivot table that contains scheme name in rows and Month in Column and aggregate value as sum of AUM but as seen from the sample data D scheme is missing for the month of feb-23. I am getting a blank value for scheme D in month of feb-23. how can we fill the missing value with 0.

|Month|Scheme|Broker ID|AUM|
|Jan-23|A|1|12|
|Jan-23|B|1|23|
|Jan-23|C|1|14|
|Jan-23|D|1|15|
|Feb-23|A|1|24|
|Feb-23|B|1|25|
|Feb-23|C|1|20|
|Mar-23|A|1|10|
|Mar-23|B|1|16|
|Mar-23|C|1|12|
|Mar-23|D|1|14|

|Sum of AUM|Column Labels||||
|Row Labels|Jan|Feb|Mar|Grand Total|
|A|12|24|10|46|
|B|23|25|16|64|
|C|14|20|12|46|
|D|15||14|29|
|Grand Total|64|69|52|185|

2

Hello Moheed,

Have you tried using coalesce function ? if not try it out and see if it helps.

Fore more info -Coalesce - Amazon QuickSight

Similar old post for your reference - Formatting empty value cell in pivots - #7 by Hassain

Hope this helps.

Cheers,
Deep

3

Coalesce didn’t worked for me.

4

@Moheed ,
If the row does not exist for the month in your dataset, coalesce will not work. You would need the combination for each date to exist in your dataset .

Kind regards,
Koushik