Top N on filtered data

[Snymoen]

Hi!

I am working on a dataset for a fashion business. In my dataset I have a dimension for "Style" and a calendar dimension.

I also have a measure for "Stock" detailing how much of a given style is in stock, along with various other stock and sales metrics

I am trying to create a list of styles with high stock and low sell-through. I have created a measure called Sellthrough%, which looks at the stock quantities at the beginning of the selected period, the sold quantities in the period and divides them to give me a percentage.

What I am now trying to create is a visual that details the Top N styles (relative to stock) below a certain sellthrough threshold:

Style	Stock	Sellthrough
Style B	12.000	41%
Style A	10.000	49%
Style C	8.000	36%

Here is what works:

• I can create the unfiltered visual just fine
• I can add filters so my table does not contain values outside of certain stock & Sellthrough thresholds (e.g. "Stock is greater than 100", "Sellthrough% is less than 50%") and sort by Stock. This returns the desired result, except the list is far too long
• I can filter my visual to only show top N styles by Stock

However, if i filter on top N (stock) and filter on sellthrough thresholds at the same time, the table return few or no results. Effectively, I can create the exact visual I want, i'm just unable to shorten it to N items without first removing my Sellthrough% filter (which defeats the purpose)

I am guessing that my filter on the Sellthrough measure (which is based on stock) and the top N (stock) filter are interfering with each other, so that there is little or no overlap between the results in the "top N" filter and the results in the "Sellthrough%" filter, meaning few or no results are returned. I would like to return the Top N of the filtered dataset.

There are tens of thousands of styles in my dataset, so it is not caused by less than N possible results.

Here is what i've tried:

• Create a rankx measure based on stock and use as Top N filter (Same result)
• Create a Top N measure based on stock
  • Kind of almost works. It returns the correct top n results and nothing else, but since I have other measures in my table (e.g. Sellthrough%) the visual still contains all styles and not only top N
  • Filtering the Top N Measure yields few or no results

Here are the measures i've created to try to solve the problem:

Sellthrough % = 
var _min = minx('Calendar','Calendar'[Date])
Return
DIVIDE([Sales Quantity],
CALCULATE([Stock Quantity],'Calendar'[Date]=_min))

_TOP 8 Styles = CALCULATE([Stock],TOPN(8,ALLSELECTED(Style),[Stock],DESC),VALUES(Style))

_rankStock = RANKX(ALLSELECTED('Style'),[Stock],,DESC,Dense)

Hope someone can help me understand what's going on!

[AmiraBedh]

You didn't provide your model so I am assuming thay you need a measure for the stock at the beginning of the period:

InitialStock =
CALCULATE(
[Stock Quantity],
FILTER(ALL('Calendar'), 'Calendar'[Date] = MIN('Calendar'[Date]))
)

You already have a measure for Sellthrough Percentage so here is an updated measure :

Sellthrough% =
DIVIDE([Sales Quantity], [InitialStock], 0)

and for Top N Filtered by Stock:

TopNStock =
CALCULATE(
[Stock Quantity],
TOPN(8, ALL('Style'), [Stock Quantity], DESC)
)

and :

RankByStock =
RANKX(ALL('Style'), [Stock Quantity], , DESC, Dense)

Then uou create a calculated table or measure that considers both the sellthrough filter and top N stock filter:

FilteredStyles =
CALCULATETABLE(
VALUES('Style'),
FILTER(
ALL('Style'),
[Sellthrough%] < 0.5 && [RankByStock] <= 8
)
)

[Snymoen]

Thank you so much for taking the time to reply!

I am unsure what you mean by "provide your model"? I am working in a Power BI semantic model if that helps.

The suggested CALCULATETABLE does not seem to work for me, I am getting the following error:

While I am not familiar with how CALCULATETABLE works, I am concerned that the solution might not work, as the RankByStock ranks all styles, meaning that the top 8 styles in my table may not have a rank <= 8.

See example from visual before limiting to top N:

I have partially censored style names for security

[AmiraBedh]

By model I mean your fact tables and dimensions.

[Snymoen]

Alright, I think i get you. Here are the four fact tables I have in use. 

They are connected via CalendarID and StyleID

The whole model is probably too convoluted to post in any meaningful way:

[Anonymous]

Hi @Snymoen 

Thanks for the reply from @AmiraBedh .

@Snymoen , Do you want to display the top 8 stock rankings in the visualization? Please try the following:

MEASURE = 
VAR _rankStock = RANKX(ALL('Style'),[Stock],,ASC,Dense)
RETURN
IF(_rankStock <= 8, 1, 0)

Put the measure into the visual-level filters, set up show items when the value is 1. 

The above measure can also be used as a screening condition for other formulas.

If you still have questions, please share the sample data and expected results of the tables you used, as well as the formulas used. How to provide sample data in the Power BI Forum - Microsoft Fabric Community The sample data only needs to include the columns that are used. We can better understand the problem and help you. Or show them as screenshots. Please remove any sensitive data in advance. 

Best Regards,
Yulia Xu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.