Show top n results by multiple categories

[Anonymous]

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

[lbendlin]

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)

[Anonymous]

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.

[lbendlin]

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" 

[Anonymous · Accepted 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)

Rank New =
Var _PerformRank =
IF (
ISBLANK( [TotalSum] ) , BLANK() ,
RANKX( ALL ( table[Name] ) , [TotalSum] , , ,Dense )
)
VAR _DisplayTop10 = IF (_PerformRank <=10 , _PerformRank , BLANK() )
VAR _DisplayAtNameLevel = IF ( ISINSCOPE( table[Name] ) , _DisplayTop10 , BLANK() )
RETURN
_DisplayAtNameLevel

4. I created a Reference table Fund with only unique Fund Names and joined it back with the original table.

TotalSum of Top 10 =
IF ( ISINSCOPE( Fund[Fund] ) ,
SUMX ( FILTER( VALUES( Name ) , [Rank New] <=10) , [TotalSum] ) , BLANK() )

That's pretty much it in the nutshel.