RANKX wrong grand total

[Anonymous]

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!

[Anonymous · Accepted solution]

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

)

[aduguid]

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