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 try a similar approach on the 3 month, I get 

934,239 / 1,417,814 = 66.4% and it should be 65.9%

The 3 Month Total and 3 Months Top 50 are correctly calculated

To get the calculation to work I am attempting to create a measure which determins what the Top 50 Products are this month

and using that list calculate the sum of the last 3 months.

My Filters to calculate the Top 50 are set as

Where C_Unit Total = 

C_Unit Total = CALCULATE (SUM('MyTable'[Sales Units]), 
       FILTER ('MyTable','MyTable'[Index_Month] = 1 || 'MyTable'[Index_Month] = 2 ||'MyTable'[Index_Month] = 3 )
    )

and C_Unit TW = 

C_Unit TW = CALCULATE(
    SUM(
    'MyTable'[Sales Units]),
    FILTER('MyTable','MyTable'[Index_Month] = 1)
)

What I am guessing at this stage is that I need to add a FILTER to a new Measure C_Unit 3M that includes the Top 50 Short_Name by C_Unit TW

And that is where I am stuck

 

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])

14 Replies