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

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Anonymous
Not applicable

Adding two columns from different sheet (using a filter)

I am trying to add two columns / sheet from a different sheet for the dashboard, but running into a 'Blank' statement if the corresponding row doesnt exist in one of the columns / sheet.

E.g.

 

Sheet 1

FruitPrice
Apple$30
Orange$45
Bannana$25
Pear$15
Water Melon$30

 

Sheet 2:

FruitPrice
Apple$20
Bannana$10
Pear$40
Watermelon$25

 

Orange doesnt exist in sheet 2

I am trying to use the following formula and it gives me 'Blank' on dashboard when the filter on the dashboard is set to orange

 

Dashboard: Filter is set to Orange

Total Price = sum('Sheet1'[Price], 'Sheet2'[Price]) 

1 ACCEPTED SOLUTION

Try

My Sum = SUM('Sheet1'[Amount]) + SUM('Sheet2'[payment])

View solution in original post

6 REPLIES 6
Anonymous
Not applicable

pin2bhatt_0-1665075632884.png

and

pin2bhatt_1-1665075676228.png

trying to get sum up these two values and I am getting an error.

pin2bhatt_2-1665075965532.png

The formula : =sum('sheet1'[Amount], 'sheet2'[payment]) works only if both these sheets have the value determined by the filter on the dashboard

Try

My Sum = SUM('Sheet1'[Amount]) + SUM('Sheet2'[payment])
Anonymous
Not applicable

works just fine. thank you .. It was so simple, I was over complicating it ...

Anonymous
Not applicable

Doesnt work 😞

you'll need to be more specific, what result or error are you getting? can you share a sample PBIX ?

johnt75
Super User
Super User

Create a dimension table like

Fruits =
DISTINCT (
    UNION ( ALLNOBLANKROW ( 'Sheet1'[Fruit] ), ALLNOBLANKROW ( 'Sheet2'[Fruit] ) )
)

and link that to both tables in a one-to-many relationship with a single direction. Use the column from the new table on any visuals or filters and it should be fine.

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

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

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.