Forum Discussion
Top n
Hello,
Does anyone know how to achieve top n by date? More details below...
| 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 |
Hi badger123
1.Create measures in Table2
Measure_value = SUM('Table 1'[Value]) rank = RANKX(ALL('Table 2'[Legend]),[Measure_value],,DESC)2.Then create a table by entering data in Modeling menu
Don't create any relationship for this table
add "top" column from this table into a slicer
3.Create measures in Table2
selected topn = SELECTEDVALUE('top_n table'[top]) flag = IF('Table 2'[rank]<='Table 2'[selected topn],1,0)Add "flag" measure in the visual level filter of that table as above
Best Regards
MaggieCommunity Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
6 Replies
Hi badger123
1.Create measures in Table2
Measure_value = SUM('Table 1'[Value]) rank = RANKX(ALL('Table 2'[Legend]),[Measure_value],,DESC)2.Then create a table by entering data in Modeling menu
Don't create any relationship for this table
add "top" column from this table into a slicer
3.Create measures in Table2
selected topn = SELECTEDVALUE('top_n table'[top]) flag = IF('Table 2'[rank]<='Table 2'[selected topn],1,0)Add "flag" measure in the visual level filter of that table as above
Best Regards
MaggieCommunity Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.badger123 - Just to clarify, a single phrase has multiple Categories. This could be problematic - could you share a screenshot of your model diagram (relationships)?
Thanks,
Nathan
badger123 - One potential solution is to add a new Calculated Table:
Top Legends = var x = SUMMARIZE(Table1, Table2[Legend], Table1[Date], "TotalValue", sum(Table1[Value])) var y = ADDCOLUMNS( x, "Rank", RANKX( FILTER( x, [Date]=EARLIER([Date]) ), [TotalValue] ) ) var z = FILTER(y, [Rank] <= 2) return zAnonymous I tried the measure, but keep getting : The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value.
Hi Anonymous , thanks for your help. Yes, a single phrase can have more than one category assigned to it... The tables are linked by phrase (many to many).
