Adding a TOPN Filter to a Measure

[LUCASM]

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

[LUCASM]

@lbendlin 

Thank you for your explaination, that is worth a lot to me.

[LUCASM]

Hi @lbendlin 

Thank you for your support, The measure does what I needed, but I'm struggling to fully understand it.

I have managed to tweek it for 4 other measures so, I cant be doing to bad.

I'll mark this as Solved.

[lbendlin]

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-...
https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

[LUCASM]

Hi @lbendlin 

Thank you for your offer to help

I have created a sanatised file and a pbix file so you can see what I am trying to do.
I hope this helps

Here is a link to a OneDrive folder

https://1drv.ms/u/s!AgfQYi2RKbVJ41vWBFkOeunfki0h?e=C1XAf2

[lbendlin]

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]

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]

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]

Current week = Index_Week 1

or MAX(YYYYWW)

[lbendlin]

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]

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.
 

[lbendlin]

Something like this ?

[lbendlin · Accepted solution]

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]

What are you using "Widget" for in 

var tt = SELECTCOLUMNS(TOPN(10,ms,[s]),"Widget",[Short Name])

[lbendlin]

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.