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 sho...
  • 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)
    )
techies's avatar
techies
Icon for Super User rankSuper 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)
)