Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 |
Solved! Go to Solution.
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
Maggie
Community 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.
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
Maggie
Community 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
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).
@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 z
@Anonymous I tried the measure, but keep getting : The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
97 | |
96 | |
81 | |
74 | |
66 |
User | Count |
---|---|
126 | |
105 | |
103 | |
81 | |
72 |