topic RANKX wrong grand total in DAX Commands and Tips

RANKX wrong grand total

Anonymous — Wed, 26 Jun 2024 17:16:04 GMT

Hello PBI-experts,

I cannot seem to fix the issue. Following constellation results in a wrong grand total in my matrix:

I have a ranking based on different dimensions (dynamic x-axis) as:

Ranking Dimensions = SWITCH(SELECTEDVALUE('X-Axis'[Dimension],BLANK()), "A",RANKX(ALLSELECTED('DIM_A'[Name]),[Switch KPI],,DESC,DENSE), 
"B",RANKX(ALLSELECTED('DIM_B'[Name]),[Switch KPI],,DESC,DENSE), 
"C",RANKX(ALLSELECTED('DIM_C'[Name]),[Switch KPI],,DESC,DENSE) )

And the measure in the matrix as:

Dynamic Ranking = SUMX(VALUES('X-Axis'[X-Axis]),
IF([Ranking Dimensions]<='X-Axis Ranking TopN'[X-Axis Ranking TopN Value], [Switch KPI]))

The slicer works as expected and throws the top N rows, however in the grand total row it misses the context of the TopN slicer and sums up everything.

Any suggestions what I could do?

Thanks alot!

Re: RANKX wrong grand total

aduguid — Thu, 27 Jun 2024 03:36:45 GMT

This may solve it

Dynamic Ranking = VAR SelectedAxis = SELECTEDVALUE('X-Axis'[X-Axis]) RETURN SUMX(VALUES('X-Axis'[X-Axis]), IF ( NOT ISFILTERED('X-Axis'[X-Axis]) || 'X-Axis'[X-Axis] = SelectedAxis, IF ( [Ranking Dimensions] <= 'X-Axis Ranking TopN'[X-Axis Ranking TopN Value], [Switch KPI], BLANK() ), BLANK() ) )

Re: RANKX wrong grand total

Anonymous — Thu, 27 Jun 2024 18:11:49 GMT

If someone comes across the same issue I´ve figured it out by creating another measure and using it in the filter pane:

TopN Filter =

var selectednumber = SELECTEDVALUE('X-Axis Ranking TopN'[X-Axis Ranking TopN])

RETURN

INT(

[Ranking Dimensions] <= selectednumber

)