Forum Discussion

LUCASM's avatar
LUCASM
Helper IV
4 years ago
Solved

Adding a TOPN Filter to a Measure

I am rewriting this as I still dont have an answer and my original post has dried up. I have the following cards  The top 50 is 297,219 / 436,661 = 68.1% of the Total for 1 week However If I...
Need Help
  • lbendlin's avatar
    lbendlin
    4 years ago

    Here is a more accurate representation that also works on line level.

     

    SU Top10 = 
var mw = MAXX(all(Weekly),[YYYYWW])
var ft = filter(all(Weekly),[YYYYWW]=mw)
var ms = SUMMARIZE(ft,[Short Name] ,"s",sum([Sales Units]))
var tt = SELECTCOLUMNS(TOPN(10,ms,[s]),"Widget",[Short Name])
var mc = SUMMARIZE(Weekly,[Short Name] ,"s",sum([Sales Units]))
var mf = filter(mc,[Short Name] in tt)
return sumx(mf,[s])
LUCASM's avatar
LUCASM
Helper IV
4 years ago

What are you using "Widget" for in 

var tt = SELECTCOLUMNS(TOPN(10,ms,[s]),"Widget",[Short Name])

 

lbendlin's avatar
lbendlin
Super User
4 years ago

SU Top10 =
var mw = MAXX(all(Weekly),[YYYYWW])    -- get the latest week identifier across the entire table
var ft = filter(all(Weekly),[YYYYWW]=mw)  -- get all transactions for the last week of the entire table
var ms = SUMMARIZE(ft,[Short Name] ,"s",sum([Sales Units]))  -- summarize last week's data by widget
var tt = SELECTCOLUMNS(TOPN(10,ms,[s]),"Widget",[Short Name])  -- get the top 10 widgets for the last week. Then throw away the sales unit sums so we end up with a single column table that only has the widget names. The title of the column could be anything, I chose "Widget" for no particular reason.
var mc = SUMMARIZE(Weekly,[Short Name] ,"s",sum([Sales Units])) -- Now switching to the current filter context. Summarize the transactions of the current filter context by widget.
var mf = filter(mc,[Short Name] in tt) -- filter the current results to only include widgets that are in the top 10 for the last week
return sumx(mf,[s])  -- final result of the computation, based on the current filter context.