Re-filter a table by a column other than the one selected in a slicer visual

[Anonymous]

I am trying to visualise pharamceutical market share data. I want the user to select a drug from a slicer visual and based on their selection, determine the amount of packs sold for the selected product and all that products' competitors. A unique code determines whether two drugs are competitors. My data looks as follows:

 I have placed the 'Product Name_medprax' field in the slicer. I want the model to sum the 'Quantity' field for all products with the same 'Medprax Generic Code' as the product selected in the slicer but not excluding the product selected in the slicer. The problem is when the user selects a product in the slicer the table is filtered by that product. I now need to remove that filter and return all rows where 'Medprax Generic Code' of the product selected in the slicer is equal. I have tired the following DAX code but it is not working:

Test = CALCULATE(
     SUM('Adcock Scripts Fact'[Quantity]);
     FILTER(
          ALL('Adcock Scripts Fact');
          'Adcock Scripts Fact'[Medprax Generic Code] = SELECTEDVALUE('Adcock Scripts Fact'[Medprax Generic Code])
     )
)

I am using the ALL function within the filter expression to remove the outer filter applied by the slicer, but this doesnt seem to be working. For instance if I select the 'SOLPHYLLEX' product I get the following result:

The green bar is the sum of quantity for just 'SOLPHYLLEX', the black is for SOLPHYLEX and its competitors. Problem is I want the black bar to be split out by 'SOLPHYLLEX' , Product A, Product B etc.

Thanks for your help

[v-yuta-msft · Accepted solution]

Hi Paul_PowerBI,

You said "I have placed the 'Product Name_medprax' field in the slicer. ", you should place field "Medprax Generic Code" instead and then the measure you have created can work.

Regards,

Jimmy Tao