Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |