Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hello,
In my data I have many records for Date, Fund, Name and Total.
I need to show Top n by Date (newest to oldest), Fund (Alphabetical) and Total (sum) for each Name.
I put together a simple visual below and looking at Top 2.
Please help.
Form
Date | Fund | Name | Total |
5/31/2022 | SC | A | 3 |
5/31/2022 | SC | A | 5 |
5/31/2022 | SC | D | 6 |
5/31/2022 | SC | D | 7 |
5/31/2022 | SC3 | B | 2 |
5/31/2022 | SC3 | C | 4 |
6/30/2022 | SC | A | 53 |
6/30/2022 | SC | B | 44 |
6/30/2022 | SC | C | 65 |
6/30/2022 | SC | D | 22 |
6/30/2022 | SC3 | B | 63 |
6/30/2022 | SC3 | C | 2 |
7/31/2022 | SC | A | 5 |
7/31/2022 | SC | B | 8 |
7/31/2022 | SC | A | 33 |
7/31/2022 | SC3 | A | 44 |
7/31/2022 | SC3 | B | 53 |
7/31/2022 | SC3 | C | 67 |
to
Date | Fund | Name | Total |
7/31/2022 | SC | A | 38 |
7/31/2022 | SC | B | 8 |
7/31/2022 | SC3 | C | 67 |
7/31/2022 | SC3 | B | 53 |
6/30/2022 | SC | C | 65 |
6/30/2022 | SC | A | 53 |
6/30/2022 | SC3 | B | 63 |
6/30/2022 | SC3 | C | 2 |
5/31/2022 | SC | D | 13 |
5/31/2022 | SC | A | 8 |
5/31/2022 | SC3 | C | 4 |
5/31/2022 | SC3 | B | 2 |
Solved! Go to Solution.
@lbendlin lbendlin that did not work but here is what i did. it's tricky.
1. Add measure: TotalSum= SUM ( table [Total]) - From what i was told no naked calculations.
2. Add rank maeasure: Strategy Rank = RANKX( ALL( table [Name] ) , [TotalSum] )
3. The crazy measure: (I actually needed top 10, 2 was easier to use in an example)
That's pretty much it in the nutshel.
You can do that in the Power BI user interface, without formulas. "Filters on this visual" allows you to switch to a TOPN type filter. (Make sure to enable the setting to allow users of your report to change filter types)
Hi,
Top n will work if the names are repleating in all categories. That is not my case. You can see that D for example only shows up in one section.
Best.
create this measure
rn = rankx(calculatetable('Table',REMOVEFILTERS('Table'[Name])),CALCULATE(sum('Table'[Total])))
and then add it to the visual level filters and set it to "less than 3"
@lbendlin lbendlin that did not work but here is what i did. it's tricky.
1. Add measure: TotalSum= SUM ( table [Total]) - From what i was told no naked calculations.
2. Add rank maeasure: Strategy Rank = RANKX( ALL( table [Name] ) , [TotalSum] )
3. The crazy measure: (I actually needed top 10, 2 was easier to use in an example)
That's pretty much it in the nutshel.