Forum Discussion

Perplexed's avatar
Perplexed
Frequent Visitor
1 year ago
Solved

Dax filter question for competitive comparison

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. 

 

MarketVendorsRevenue
XA1
XB2
XC5
XHome3
YA4
YD2
YE1
YHome10
ZB2
ZF6
ZG2
ZHome1
  • techies's avatar
    techies
    1 year ago

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

2 Replies

  • DataNinja777's avatar
    DataNinja777
    Super User
    1 year ago

    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,

  • techies's avatar
    techies
    Super User
    1 year ago

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