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])
lbendlin's avatar
lbendlin
Super User
4 years ago

Please provide sanitized sample data that fully covers your issue. I can only help you with meaningful sample data.
Please paste the data into a table in your post or use one of the file services like OneDrive or Google Drive. Screenshots of your source data are not useful.
Please show the expected outcome based on the sample data you provided. Screenshots of the expected outcome are ok.

https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216
https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

    • lbendlin's avatar
      lbendlin
      Super 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
        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