Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
kukszi
Helper I
Helper I

Using measure as a slicer

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?

 

5 REPLIES 5
kukszi
Helper I
Helper I

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
Helper I
Helper I

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?

Hi @kukszi,

 

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

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
Resolver I
Resolver I

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 😎

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

Top Solution Authors