Forum Discussion

LUCASM's avatar
LUCASM
Icon for Helper IV rankHelper 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])
lbendlin's avatar
lbendlin
Icon for Super User rankSuper User
4 years ago

When you say "Top 10 (12 wks)"  do you mean the top 10 for the entire range of 12 weeks, or do you want to consider all accounts that are in the Top 10 in each of the 12 weeks?

  • LUCASM's avatar
    LUCASM
    Icon for Helper IV rankHelper IV
    4 years ago

    Hi lbendlin 

    Apologise for the delay in responding

    What I am attempting to do is CALCULATE the SUM(Volume) of ONLY the TOP 10 "Short Names" in the Latest Week over the last 12 weeks 

    DIVIDED BY

    The Total SUM(Volume) over 12 weeks.

    So Basically if you look at the Filters used to create the Top 10 Volume (12 Wks) - 302,717

    can this be written as a Measure?

     

    edit:

    the confusing thing is that I only want to incude those products which appear in the current week top 10 and not what is the the real market share over 12 weeks.

    This is because the table shows the current top 10 by volume and what their sales were over the last 12 weeks.

    So the card above - in the pbix, needs to show the sum of the top 10 in the table 

    and then divide that top 10 by the Total  

    • lbendlin's avatar
      lbendlin
      Icon for Super User rankSuper User
      4 years ago

      That makes it easier to calculate but it will be more confusing for your users. make sure to add wordage to the page explaining what they are looking at.

       

      Now you would need to define what you mean by "current week".

       

      • LUCASM's avatar
        LUCASM
        Icon for Helper IV rankHelper IV
        4 years ago

        Current week = Index_Week 1

        or MAX(YYYYWW)