Dax filter question for competitive comparison

[Perplexed]

I have some competitive data - simplified below. 

If I have a simple matrix with market as the rows and a slicer to allow you to choose the vendor. 

So if I choose Vendor A - the table would show Market X, Revenue 1 and then Market Y, Revenue 4. 

The "Home" vendor is in all markets - What measure would allow me to include the "Home" vendor  comarison revenue but only for the markets where the Vendor selection in the slicer plays. 

I've tried various combinations of Filter, All, AllExecpt but have not hit the right one yet. 

Thanks in advance for any pointers. 

Market	Vendors	Revenue
X	A	1
X	B	2
X	C	5
X	Home	3
Y	A	4
Y	D	2
Y	E	1
Y	Home	10
Z	B	2
Z	F	6
Z	G	2
Z	Home	1

[techies · Accepted solution]

Hi @Perplexed please check this

Selected Vendor Revenue =

VAR SelectedVendor = SELECTEDVALUE('Vendor Selector'[Vendor])

RETURN

CALCULATE(

    SUM('Sheet8'[Revenue]),

    'sheet8'[Vendor] = SelectedVendor

)

Home Vendor Comparison Revenue =

VAR SelectedVendor = SELECTEDVALUE('Vendor Selector'[Vendor])

VAR MarketsWithVendor =

    CALCULATETABLE(

        VALUES('Sheet8'[Market]),

        'Sheet8'[Vendor] = SelectedVendor

    )

RETURN

CALCULATE(

    SUM('sheet8'[Revenue]),

    'sheet8'[Vendor] = "Home",

    KEEPFILTERS(MarketsWithVendor)

)

[DataNinja777]

Hi @Perplexed ,

To show the "Home" vendor's revenue only in the markets where the selected vendor from the slicer appears, you'll need a measure that checks two things: whether the row is the selected vendor, or whether it's the "Home" vendor in a market where the selected vendor operates. First, use SELECTEDVALUE to capture the vendor selected in the slicer:

SelectedVendor = SELECTEDVALUE(Data[Vendor])

Next, write your main measure that filters the data based on whether the current row is for the selected vendor or for "Home" in a relevant market:

ShowRevenue =
VAR SelectedVendor = SELECTEDVALUE(Data[Vendor])
VAR CurrentVendor = MAX(Data[Vendor])
VAR CurrentMarket = MAX(Data[Market])
VAR MarketsWithSelectedVendor =
    CALCULATETABLE(
        VALUES(Data[Market]),
        Data[Vendor] = SelectedVendor
    )
RETURN
IF (
    CurrentVendor = SelectedVendor
    || (
        CurrentVendor = "Home"
        && CurrentMarket IN MarketsWithSelectedVendor
    ),
    SUM(Data[Revenue])
)

Add this measure to a matrix visual with Market and Vendor as rows and this ShowRevenue measure as values. Then, add a slicer on the Vendor field. When a vendor like "A" is selected, the matrix will show their revenues in relevant markets and also show the "Home" vendor's revenue in those same markets, ignoring all others.

Best regards,