Calculate a sum minus a count filter from another table

[Anonymous]

I've 2 tables which need to be calculated with each other. Table 1, column 2 amount needs the total (SUM), which needs to subtracted with all the Yes from Table 2, column 2 text (count). I made a DAX formula but it shows a blank value in the card visual. Hope someone can help out?

DAX:

TEST sum =
CALCULATE (
SUM ( 'Table 1'[Col 2 amount] ) - COUNT ( 'Table 2'[Col 2 text] ),
FILTER ( 'Table 2', 'Table 2'[Col 2 text] = "Yes" )
)

SETUP:

Table 1

Col 1 | Col 2 amount

1       |  20

2       |  30

3       |  40

4       |  25

5       |  20

Table 2

Col 1 | Col 2 text

1       |  Yes

2       |  Yes

3       |  No

4       |  No

5       |  No

[amitchandak · Accepted solution]

@Anonymous , Try like

TEST sum =
CALCULATE (
SUM ( 'Table 1'[Col 2 amount] )) - CALCULATE (COUNT ( 'Table 2'[Col 2 text] ),
FILTER ( 'Table 2', 'Table 2'[Col 2 text] = "Yes" )
)

you need to have a common dimension to display measures together

Bridge Table: https://www.youtube.com/watch?v=Bkf35Roman8&list=PLPaNVDMhUXGaaqV92SBD5X2hk3TMNlHhb&index=19

[Anonymous]

Thank you, this helped