Variable threshold to show fastest growing brands

[Anonymous]

Hi,

I have a table with several calculated metrics that I use to see which brands have grown the fastest over the last X months. I use this table to identify upcoming new brands, brands we need to watch out for as they grow faster than the category. The table looks somewhat like this:

Brand name	Brand sales this time period	Brand sales in previous time period	Growth	Growth vs category
Brand A	$600	$1	59900%	59890%
Brand B	$40,000	$4,000	900%	890%
Brand C	$600	$300	100%	90%
Brand D	$700,000	$600,000	17%	7%
Brand E	$1,900,000	$1,800,000	6%	-4%
Total	$2,641,200	$2,404,301	10%

The time period is flexible using a dropdown in the report. This means that users can change the time period, and the results per brand and the totals will change, along with the growth numbers.

As you can see, this table shows that Brand A grew the most. However, this Brand moved from just 1USD in sales to 600USD. That's nothing compared to some of these other brands that do over 1m USD in sales, and it's therefore of no interest to me or my stakeholders. I therefore want to be able to add a threshold: If the sales in the previous time period were less than X, then don't display those numbers in the chart.

As a temporary solution I simply added a filter to the visualisation that says if the sales in the previous time period is lower than 30k USD, don't display any numbers. However, when diving deeper into the numbers, that 30k threshold is too high, and for some of the customers in the system I'd like this threshold to be lower.

Is it possible to create some sort of threshold that only shows brands when the sales of that brand is higher than let's say 20% of all the brands in that category, and apply that as a filter to this visual?

Best regards

Bas

[V-pazhen-msft · Accepted solution]

@Anonymous  

What I can think of is to create add filter to the growth to filter out the row with previous sales more than xxx,  something like:

Measure =calculate(max([growth]), Filter(Table, [Brand sales in previous time period] > xxx))

For  sales are higher than 20% of all the brands, you can create a if measure:

Measure = If(sum([Brand sales this time period])/sumx(all(table),[Brand sales this time period]) > 0.2,1)

Paul Zheng _ Community Support Team
If this post helps, please Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Thank you @V-pazhen-msft ! I followed your advice and landed on an alternative solution that isn't what I wanted exactly but also does the trick. I created a new table that contained a list of the thresholds (1k / 10k / 30k / 50k / 100k). I added that threshold column as a filter to the page. I then created a measure as you said, that is checking the the value of the threshold, and only shows brands where the sales value is higher than the threshold. I applied that measure as a filter to the visual, if it's blank then it's not showing any data.

Thank you for getting me in the right direction!

Best regards

Bas

[amitchandak]

@Anonymous , refer to my blog for customer retention. Here Brand is the customer. For Last month I have checking for blank, You check for some small value. See if that can help

Customer Retention Part 1:
https://community.powerbi.com/t5/Community-Blog/Customer-Retention-Part-1-Month-on-Month-Retention/ba-p/1361529

[Anonymous]

@amitchandak I'm sorry but this blog post has nothing to do with the problem I'm facing.

I'm looking to find a way to show values for the brands in my system (or customers), but only showing it when the brands have generated a minimum amount of sales. The problem is that that minimum amount of sales is variable. One time it will be that it has to be more than 10k, and in other circumstances it will have to be over 100k. So therefore the suggestion would be to add a filter to the visual that makes sure that only the brands are shown where the sales are higher than 20% of all the brands based on the current time frame. Would you know whether it's possible to do something like that?

Best regards