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

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
Super 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
    Helper IV
    4 years ago

    Current week = Index_Week 1

    or MAX(YYYYWW)

     

    • lbendlin's avatar
      lbendlin
      Super User
      4 years ago

      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?

      • LUCASM's avatar
        LUCASM
        Helper IV
        4 years ago

        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.