topic Re: Adding two columns from different sheet (using a filter) in DAX Commands and Tips

Adding two columns from different sheet (using a filter)

Anonymous — Thu, 06 Oct 2022 15:18:31 GMT

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

Re: Adding two columns from different sheet (using a filter)

johnt75 — Thu, 06 Oct 2022 15:25:51 GMT

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.

Re: Adding two columns from different sheet (using a filter)

Anonymous — Thu, 06 Oct 2022 15:46:52 GMT

Doesnt work 😞

Re: Adding two columns from different sheet (using a filter)

johnt75 — Thu, 06 Oct 2022 15:56:42 GMT

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

Re: Adding two columns from different sheet (using a filter)

Anonymous — Thu, 06 Oct 2022 17:10:10 GMT

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

Re: Adding two columns from different sheet (using a filter)

johnt75 — Fri, 07 Oct 2022 08:34:15 GMT

Try

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

Re: Adding two columns from different sheet (using a filter)

Anonymous — Mon, 10 Oct 2022 18:11:58 GMT

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