Display dimension if calculated measure is within range

[dsalazar3]

Hello,

I've did a bit of googling and searching through this forum, but haven't found the solution I am looking for.

I have a dataset that contains competitor products(dimension), date(dimension), and the amount of units(measure) by date. I've created the following expression to show market share even if I filter down to 1 or 2 products.

Expression for Market Share = Sum(Units)/Sum({<ProductName=>} TOTAL <Date> Units)

I am trying to create 3 line charts. Top performers (products with 20% and greater market share), middle of the pack (20% - 10%), bottom performers (less that 10%). Using the Limitation feature, I am able to filter on Product Name based on Exact Value to show the top performers and bottom performers. I can't figure out how to filter or limit the "Middle of the Pack" graph by a range of values (10%-20%).

Any help would be greatly appreciated! Thank you.

[Andrei_Cusnir · Accepted solution]

Hello,

Instead of using the Limitation feature, you can also limit the values right within the measure expression of each Line chart visualization. 

For example:

1. Create 3 Line chart visualizations and add ProductName field as dimension for all of them.

2. For the measure expression use the following expressions respectively: 

• =If(EXPRESSION > 0.2, EXPRESSION , Null())
• =If(EXPRESSION  >= 0.1 and EXPRESSION  <= 0.2, EXPRESSION , Null())
• =If(EXPRESSION  < 0.1, EXPRESSION , Null())

Where EXPRESSION is your custom expression for calculating MarketShare values. Basically each expression first checks if the result of the caluclation is within the range that you want and in that case returns the calculated result. Otherwise, it will return null value which will be excluded from the chart later.

3. Then for all the Line charts, go to Add-ons > Data handling and Un-check the option "Include zero values"

4. This is the outcome:

As you can see the chart with >20% shows only the ProductB which has MarketShare of 28.69%, the chart >10%<20% shows the products ProductC, ProductD and ProductF which have the MarketShare respectively 10.25%, 16.39% and 12.30% and the last chart shows products ProductA, ProductE, ProductG and ProductH with MarketShare being respectively 8.20%, 6.15%, 8.20% and 9.84%. This means that all charts are working as expected!

I hope that this information was helpful. In case I have misunderstood the use case scenario, please elaborate in details by providing additional information. However, if it has helped you resolve the issue, please mark it as accepted solution to give further visibility to other community members. 
 

[dsalazar3]

@Andrei_Cusnir  This is perfect and much simpler than what I was trying to do. Thank you!