Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Ranking Total Issue.

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

 

  • Hi:

    Would this streamline it a bit? For each level

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

5 Replies

  • 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's avatar
      Anonymous
      Not applicable

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

       

      • Whitewater100's avatar
        Whitewater100
        Solution Sage

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

        )

        )