cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
garythomann_vac
Advocate III
Advocate III

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

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...

garythomann_vac_0-1620110690870.png

 

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...
garythomann_vac_2-1620111546306.png

 

Matrix display with rankx portion added as column...

garythomann_vac_3-1620111615823.png

 

1 ACCEPTED SOLUTION
garythomann_vac
Advocate III
Advocate III

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]
)

View solution in original post

1 REPLY 1
garythomann_vac
Advocate III
Advocate III

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]
)

Helpful resources

Announcements
PBI Sept Update Carousel

Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

Learn Live

Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

Top Solution Authors