Using measure as a slicer

[kukszi]

Hi there,

I'd like to create a measure for price ranges that I can use as a filter criteria in a slicer or to show the price distribution in a chart.

• The approach I try to do is the following:
  I have the total volume and total sales value by product. From this, I have created a measure to show me the average price: [Average Price]
• I have created a table (PriceRange) that has only one column (Price Range) with the following rows:
  $0-$15, $15-$20, $20-$25, $25-$30, $30-$35, $35+
• then I created a new measure
  Price Tiers =
  SWITCH(
  SELECTEDVALUE(PriceRange[Price Range]),
  "$0-$15", [Average Price]>0 && [Average Price] <=15,
  "$15-$20", [Average Price]>15 && [Average Price] <=20,
  "$20-$25", [Average Price]>20 && [Average Price] <=25,
  "$25-$30", [Average Price]>25 && [Average Price] <=30,
  "$30-$35", [Average Price]>30 && [Average Price] <=35,
  "$35+", [Average Price]>35
  )
• then I have created a slicer, added "Price Range" to the Field column
• also, I have created a pie chart where I want to see that how the total volume (measure name: [Volume]) is distributed among the price ranges. However, if I'm adding "Price Range" (the one from the table) as Legend, it will show me 6 equal sized slices on the chart, and if I try to add the above "Price Tiers" measure as Legend, Power BI does not allow it for me.

I have a feeling that somewhere I'm missing one step as technically the Price Tiers measure is not assigned to the Price Range table. Can someone help me what am I missing here?

[kukszi]

Hi @TaylorPBI21 ,

I saved the sample report to this link: Market Analysis_sample_data.pbix. The time frames and the last year comparisons won't work for now as I am including only one month of data.

Basically the volume (data - Volume) and the value (data - RV) tables are not directly connected to each other, but both of them are connected to the product master data table (param - ProductHierarchy - SKU) tab. 

The Average Price measure is calculated from the RV and the Volume measures.

What I'd like to achieve is to make the "Price Tiers" slicer and the "Volume by Price Segment" pie chart work, and be able to filter and display the data by the price categories ($0-$15, $15-$20, $20-$25, $25-$30, $30-$35, $35+).

Thank you very much for your help in advance!

[kukszi]

Hi @TaylorPBI21 ,

Thanks for your reply. Actually, I forgot to mention that the structure of my report is a bit tricky, that doesn't make it possible (or at least I don't know how to make it work). The main problem is that I have the sales volume and sales value in different tables, and measures calculate everything else (e.g the average price as well). If I understand correctly, to make your approach work, I would need to create a combined table. But I have already set up measures to summarize the data the way how the user wants to see (e.g different timeframes e.g last 1 month, last 3 months, YTD, etc; by city; store type; product size), which means that in this new table I'd need to re-create all those parameters as well, what I'd like to avoid. 

Is there any solution where I could use the approach I have started with the separate table?

[TaylorPBI21]

Hi @kukszi,

What's the relationship between the 2 tables you're using?😊

[TaylorPBI21]

Hi @kukszi ,

Could you sample data say 5 rows from each table and then the measure you are using to get the average? 😎

Many Thanks,

Taylor

[TaylorPBI21]

Hi @kukszi ,

You could just delete your price range table and use this new calculated column instead...😎

Price Tiers = 
Switch(
TRUE(),
'Table'[Average Price] > 0 && 'Table'[Average Price] <= 15, "$0-$15",
'Table'[Average Price] > 15 && 'Table'[Average Price] <= 20, "$15-$20",
'Table'[Average Price] > 20 && 'Table'[Average Price] <= 25, "$20-$25",
'Table'[Average Price] > 25 && 'Table'[Average Price] <= 30, "$25-$30",
'Table'[Average Price] > 30 && 'Table'[Average Price] <= 35, "$30-$35",
'Table'[Average Price] > 35, "$35"
)

Many Thanks,

Taylor 😎