Forum Discussion
Top n by date
E.g. top 10 in each given month, rather than top 10 based on the entire period (even if it isn’t in the top 10 in a given month).
2 Replies
Hi badger123 ,
Based on my test, you could refer to below test:
Sample data:
Create below measure:
Measure = COUNTROWS(FILTER(ALL(Table1),'Table1'[Legend]=MAX('Table1'[Legend])&&ISONORAFTER('Table1'[Value],SELECTEDVALUE(Table1[Value]),ASC,[Date],SELECTEDVALUE(Table1[Date]))))And set the measure in filter with below condition:
Result:
You could also download the pbix file to have a view.
Rrgards,
Daniel He
Thanks v-danhe-msft - this is really helpful! I can't seem to apply it to my own use case and get it to work. Here is a bit more information about my tables and desired output:
Table 1 Date Phrase Value Jan-19 phrase 1 10 Jan-19 phrase 2 20 Jan-19 phrase 3 30 Jan-19 phrase 4 40 Jan-19 phrase 5 70 Feb-19 phrase 1 100 Feb-19 phrase 2 30 Feb-19 phrase 3 20 Feb-19 phrase 4 10 Feb-19 phrase 5 5 Mar-19 phrase 1 150 Mar-19 phrase 2 20 Mar-19 phrase 3 30 Mar-19 phrase 4 40 Mar-19 phrase 5 200 Table 2 Phrase Legend phrase 1 Category A phrase 2 Category B phrase 3 Category B phrase 2 Category C phrase 4 Category C phrase 5 Category D phrase 3 Category E Current approach: Tables 1 and 2 are connected by phrase. I am using the categories as the legend and summed values as the value. I'm currently using a top n filter (e.g. top 2) which is showing the top 2 based on total value over the entire period.
Desired output: What I'm trying to do is show the top n at each given date. So based on the sample of tables I shared, this would look something like:
Date Top 2 Total Value Jan-19 Category D 70 Jan-19 Category C 60 Feb-19 Category A 100 Feb-19 Category B 50 Mar-19 Category D 200 Mar-19 Category A 150
