matrix - top n rankings over one column with other columns present

[garythomann_vac]

https://community.powerbi.com/t5/Community-Blog/Dynamic-TopN-made-easy-with-What-If-Parameter/ba-p/3...

Have setup a top n filter as per the link above.  Wondering what has to be changed within the dax to make this work when another column is added to the matrix?

I can see that the rankx portion gets split over the new column for the sumx amounts when all we want is the rankings to be only over the matrix totals for Customer within the filtered context; Date, Company, etc.  ie if   Document Type Name is not part of the matrix.

Matrix visual setup...

AmountSumx = SUMX('G_L Entry', [Amount])

AmountTopN =

VAR SelectedTop = SELECTEDVALUE('TopN'[TopN])

RETURN

SWITCH(TRUE(),

SelectedTop = 0, [AmountSumx],

SelectedTop >=

RANKX (

ALLSELECTED( 'Customer'[Customer Name] ) // table

,[AmountSumx] // expression

, // value

,0 // order desc

,Dense // ties

), [AmountSumx]

)

Top 3 selected...

Matrix display with rankx portion added as column...

[garythomann_vac · Accepted solution]

Work around I figured out as follows.  I created a rankx column using the rankx portion.  Then add the rankx column as a Value to the matrix.  Then squish the resulting columns to hide the new rankx column.  Also code change to include isinscope for the AmountTopN measure.  And set AmountSumx to zero for blanks.  And exclude zero values from matrix via Filter to exclude zero value rows.  And lastly turn off Word wrap for Column Headers to reduce the spacing bloat.

AmountSumx = SUMX('G_L Entry', [Amount])+0 // need to also set in Filters to ignore zero values

rankx =
RANKX (
ALLSELECTED( 'Customer'[Customer Name] ) // table
,[AmountSumx] // expression
, // value
,0 // order desc
,Dense // ties
)

AmountTopN =
VAR SelectedTop = SELECTEDVALUE('TopN'[TopN])
RETURN
SWITCH(TRUE(),
SelectedTop = 0, [AmountSumx],
IF(HASONEVALUE('Customer'[Customer Name]) // hide rankx column from matrix
,SelectedTop >=
RANKX (
ALLSELECTED( 'Customer'[Customer Name] ) // table
,[AmountSumx] // expression
, // value
,0 // order desc
,Dense // ties
)
,0)
, [AmountSumx]
)

[anvikuttu]

Hi,

This solution didn't work. It could be great if you shared the pbix for our review.

[azadthakur]

Can you share PBix please, wanna look this solution.