Pivot table with blank values

[Syndicate_Admin]

Good afternoon

I request help for the next problem I have. I created a pivot table in which I loaded a table that contains statefully stated user records. Based on this structure, in the pivot table I have specified at the line level the state and at the column level users. As for the values, I have entered an amount type field.

All data is tailed, the system correctly displays the table. However, those users who do not have a record with one of the states, the cell displays it blank. I understand that this happens because the pivot table detects that there is no user vs status related record.

Is there any way to be able to make me load a default value in these cases?

Thanks a lot.

[Fowmy · Accepted solution]

@Syndicate_Admin 

I would take an approach as follows. Create a new table with all state and user combinations then add the default value for the missing amount. You can replace the __defaul variable value to your desired amount.  I attached the PBIX file below my signature.

TableNew = 
 var __default = 99
 var __table = 
    ADDCOLUMNS(
        CROSSJOIN( VALUES(Table1[state ]) , VALUES(Table1[users]) ) ,
        "amount",
        VAR __state = [state ]
        VAR __user = [users]
            return 
            COALESCE( CALCULATE( SUM(Table1[amount]), Table1[state ] = __state , Table1[users] = __user) , __default)
    ) 
return
    __table

[Syndicate_Admin]

Thank you very much for the information. I'm going to try it and tell you how I'm doing.