Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
Solved! Go to 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
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
@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
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
Thank you very much for the information. I'm going to try it and tell you how I'm doing.
User | Count |
---|---|
141 | |
113 | |
104 | |
78 | |
64 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |