Forum Discussion
Adding a TOPN Filter to a Measure
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])
Current week = Index_Week 1
or MAX(YYYYWW)
your data will fluctuate wildly on the first few days of that week. Not sure this brings your business much insights. Are you planning for import mode or direct query mode?
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])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.Hi
The data is a single import from a third party data suppler each week so there will not be any flutuation - unless there is a data error and a new file is sent, which has never happened....yet.
The pbix uses Import Mode and a refresh schedule is set for every Day - to allow for Bank holidays, the supplied data set gets uploaded late or a new corrected data set is sent.
Something like this ?
What are you using "Widget" for in
var tt = SELECTCOLUMNS(TOPN(10,ms,[s]),"Widget",[Short Name])