Forum Discussion

Snymoen's avatar
Snymoen
New Member
2 years ago

Top N on filtered data

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:

 

StyleStockSellthrough
Style B12.00041%
Style A10.00049%
Style C8.00036%

 

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!

Need Help

5 Replies

  • AmiraBedh's avatar
    AmiraBedh
    Super User
    2 years ago

    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's avatar
      Snymoen
      New Member
      2 years ago

      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