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

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
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!

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?

Resolver I

Hi @kukszi,

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

Resolver I

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

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

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Monthly Update - August 2024

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

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

Fabric Community Update - September 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors