Pivot table with blank values

Question

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 Answer

Syndicate_Admin&nbsp;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.&nbsp; 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
&nbsp;

Forum Discussion

Pivot table with blank values

2 Replies

Related content

Table Pivot, result explanation

Pivot issue

Pivot / Unpivot?

DAX: Pivot table with multiple values.

Pivot tables like excel