Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Syndicate_Admin
Administrator
Administrator

Pivot table with blank values

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.

1 ACCEPTED SOLUTION
Fowmy
Super User
Super User

@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

 




Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

View solution in original post

2 REPLIES 2
Fowmy
Super User
Super User

@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

 




Did I answer your question? Mark my post as a solution! and hit thumbs up


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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.