Adding two columns from different sheet (using a filter)

[Anonymous]

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

Fruit	Price
Apple	$30
Orange	$45
Bannana	$25
Pear	$15
Water Melon	$30

Sheet 2:

Fruit	Price
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]) 

[Anonymous]

and

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

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

[johnt75 · Accepted solution]

Try

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

[Anonymous]

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

[Anonymous]

Doesnt work 😞

[johnt75]

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

[johnt75]

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.