Ranking Total Issue.

[Anonymous]

Hi All,

I'm having incorrect Rankx Total on my measure whenever i filter on fiscal Year 2022, then when i filter fiscal year 2021 it works pperfectly. The RankTotal changes to 13 which is incorrect instead of 12 for only 2022. see screem shot & code below. 
VAR _DivisonRanking =
IF (
NOT ISBLANK( [Percent] ),
CALCULATE (
RANKX (
FILTER ( ALL ( dim_sites[Division] ), NOT ISBLANK( [Percent] ) ),
[Percent],
,
DESC,
DENSE
),
ALL ( dim_sites[Region] ),
ALL ( dim_sites[Site_District] ),
ALL ( dim_sites[Site_Name] )
)
)
VAR _RegionRanking =
IF (
NOT ( ISBLANK( [Percent] ) ),
CALCULATE (
RANKX (
FILTER ( ALL ( dim_sites[Region] ), NOT ( ISBLANK( [Percent] ) ) ),
[Percent] ,
,
DESC,
DENSE
),
ALL ( dim_sites[Site_District] ),
ALL ( dim_sites[Site_Name] ),
ALLSELECTED ( dim_sites[Division] )
)
)
VAR _MarketRanking =
IF (
NOT ( ISBLANK( [Percent] ) ),
CALCULATE (
RANKX (
FILTER ( ALL ( dim_sites[Site_District] ), NOT ( ISBLANK( [Percent] ) ) ),
[Percent],
,
DESC,
DENSE
),
ALL ( dim_sites[Site_Name] ),
ALLSELECTED ( dim_sites[Region] ),
ALLSELECTED ( dim_sites[Division] )
)
)
VAR _SiteRanking =
IF (
NOT ( ISBLANK( [Percent] ) ),
CALCULATE (
RANKX (
FILTER ( ALL ( dim_sites[Site_Name] ), NOT ( ISBLANK( [Percent] ) ) ),
[Percent],
,
DESC,
DENSE
),
ALLSELECTED ( dim_sites[Region] ),
ALLSELECTED ( dim_sites[Site_District] ),
ALLSELECTED ( dim_sites[Division] )
)
)
VAR _Results =
SWITCH (
TRUE (),
HASONEFILTER ( dim_sites[Site_Name] ), _SiteRanking,
HASONEFILTER ( dim_sites[Site_District] ), _MarketRanking,
HASONEFILTER ( dim_sites[Region] ), _RegionRanking,
HASONEFILTER ( dim_sites[Division] ), _DivisonRanking,
BLANK ()
)
RETURN
_Results

[Whitewater100 · Accepted solution]

Hi:

Would this streamline it a bit? For each level

 Site Rank = IF (
NOT ( ISBLANK( [Percent] ) ), RANKX(ALL(dim_sites[Site_Name] ), [Percent],  DESC))

[Anonymous]

@Whitewater100   it works for 1 row. but, i need it to work for multiple rows total. see screen shot. 

[Whitewater100]

OK. Maybe the INSCOPE is better:

Rank In Scope = IF(

ISINSCOPE(dim_sites[Site_District]), RANKX(ALL(dim_sites[Site_District]),

[Percent]),

IF(

ISINSCOPE(dim_sites[Region]),

RANKX(ALLSELECTED(dim_sites[Region]),

[Percent]),

IF(

ISINSCOPE(dim_sites[Division] ),

RANKX(ALLSELECTED(dim_sites[Division] ),

[Percent])

)

)

[Anonymous]

@Whitewater100  The incsope didn't work either , the first code is better. can we not rank blanks for first code. 

[Whitewater100]

Hi lklizo:

If possible, would you be able to send me reprentative data? I will get it but easier if I can see your set up. Thanks for your patience..