Forum Discussion
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
| 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])
Try
My Sum = SUM('Sheet1'[Amount]) + SUM('Sheet2'[payment])
6 Replies
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.
you'll need to be more specific, what result or error are you getting? can you share a sample PBIX ?
Try
My Sum = SUM('Sheet1'[Amount]) + SUM('Sheet2'[payment])