Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.