Forum Discussion
Simple TOPN
FilteredTable = TOPN( 5, complaints, complaints[cases], DESC )
Hi johnmelbourne
You can use Rank as a measure like below.Rank Cases by Tier2= RANKX( CALCULATETABLE( VALUES( complaints[Tier2] ), ALLSELECTED() ), CALCULATE( SUM( complaints[Cases] ) ),, DESC )Or Column
Rank Cases by Tier2 = RANKX( VALUES( complaints[Tier2] ), CALCULATE( SUM( complaints[Cases] ) ),,, Dense )Regards,
Mariusz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi johnmelbourne
You can use Rank as a measure like below.
Rank Cases by Tier2=
RANKX(
CALCULATETABLE(
VALUES( complaints[Tier2] ),
ALLSELECTED()
),
CALCULATE(
SUM( complaints[Cases] )
),,
DESC
)Or Column
Rank Cases by Tier2 =
RANKX(
VALUES( complaints[Tier2] ),
CALCULATE( SUM( complaints[Cases] ) ),,,
Dense
)Regards,
Mariusz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thank you Mariusz for the additional procedure. Love to learn. Your solutions were perfect. My TOPN approach kept on throwing an error saying multiple columns. Then you provioded your solution and I also read this article which explained why TOPN is useless by itself. https://www.dutchdatadude.com/power-bi-pro-tip-confusion-about-topn-versus-rankx/
Your contribution was perfect. Thanks Mariusz
Hi Mariusz
I thought the TOPN to filter the table would be easy, but I am still struggling.
Here is my visualisation table using your rankx formula (which works great!). How would I use a TopN to reduce this table to say a top 5, or even better, a dynamic N using a variable? / slider?
Thanks
John
Hi johnmelbourne
Please see the below.Top N Sales = VAR n = MAX( 'Top N Selection'[Select Top N] ) -- Unrelated Table with one column and values for top n selection, example (1, 5, 10, 15) VAR tbl = TOPN( n, ALLSELECTED( 'Product'[Brand] ), -- replace with complaints[Tier2] [Sales], --replace with CALCULATE( SUM( complaints[Cases] ) ) DESC ) RETURN CALCULATE( [Sales], --replace with SUM( complaints[Cases] ) KEEPFILTERS( tbl ) )Regards,
Mariusz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
