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

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.

Post Prodigy
Post Prodigy

Show same column as two in the table without summarizing

Hi folks,

How do I show same table column as two columns with different filtering in the table visual? For example if I have positive amounts for income, and negative amounts for expense in the same column in the Sales table, how can I show Income in one column and Expense in the second column in the table visual? Trying to add Sales(Amount) column twice into the table visual does not allow me to filter one column as Amount>0 and the other as Amount<0. I also need amounts not summarized. Here's an example:

Initial table                      Expected result

Date    Amount                       Date    Income      Expense
1/1        100                        1/1    100
1/2        -20                        1/2                 -20
1/3        -30                        1/3     80          -30
1/3         80 
Super User
Super User

Can be achieved in power query by adding conditional columns

@mussaenda , but that is a very expensive solution, isn't it ?

Not applicable

Income = IF(SUM(Table1[Amount])>=0,SUM(Table1[Amount]),0)

Expenses = IF(SUM(Table1[Amount])<0,SUM(Table1[Amount]),0)

Try creating two measure using DAX and sue them in table.

Thanks @Anonymous , but I need data not summarized.

Not applicable

Then add a calculated column use add column (using conditional column) in edit queries.

@Anonymous , no other way to do it? It is expensive to add column to a multimillion row table.

Not applicable

You can either add a column(Storage expensive) or create a measure suggested above(computation expensive), thats a trade-off you need to check/test and select 

Well, I think if there is no easy way to do what I need (with no summarization), I am probably better off making two separate table visuals. 

Helpful resources

Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City


Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

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