How To Get Slicer To Affect Only One Column of Table

[ryanjparks]

Hello all,

I built a dashboard that grabs the Total Sales for a chosen customer (based on a Customer Slicer) and also includes a calculated Measure for Average Sales Price per SKU. This dashboard is filtered for Invoice Date on a relative "last 12 calendar months" timeframe. I also included a Measure that calculates the sales price for ALL customers for comparative purposes....this works without issue. 

What I am trying to do is get another column that calculates the average sales price for All Customers, but have it filtered for specific states. I need it to be built so that when I select the states in the slicer, it does not change the rows of data that are showing up. Currently, I can add states and it will re-average the "All Customers Average" field to include the other customer data into the average from the states I choose.....however it REQUIRES that I include the state that the selected customer is in.

For example, if I choose a customer in Texas, that customers purchased items comes up with the totals just fine. Then if I select Oklahoma in the State slicer, all the data disappears. But if I select Texas and Oklahoma, everything works fine but it is getting the average sale price for both states, which I may not want. If I disable the interaction between the state slicer and the table data, nothing happens when I select different states, so that cannot be the answer.

How do I make it so that no matter what state is chosen, it only affects the "Avg Price for Selected State" values for each given SKU, but not add or delete the row data based on the Customer Slicer?

Thanks for any help!

[Anonymous · Accepted solution]

Hi  @ryanjparks ,

I created some data:

Are you referring to the fact that the slicer selection does not show the data in the table, only the results of the table. You can create a new table as a slicer that is not related to the original table, so that it does not affect the columns of the original table, and then affect the values by creating the measure

Here are the steps you can follow：

1. Create calculated table.

Table 2 =
DISTINCT('Table'[Status])

2. Create measure.

Measure =
var _select=SELECTCOLUMNS('Table 2',"status",'Table 2'[Status])
return
AVERAGEX(
    FILTER(ALLSELECTED('Table'),
    'Table'[Status] in _select),[Value])

3. Result:

If it doesn't meet your desired outcome, can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data. We can better understand the problem and help you

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly