Rankx TOP 5 Value Help

[bdehning]

I want Top 5 Reporting Location Cities by Count and for each City the Top 5 Cause by Count and break ties by using Total Incurred

This is used to get Reporting Location City using Top N and Top 5 by Value of Measure

Sort measure Count Location Cause =

VAR _FreqByCity = CALCULATE([Count of Total Gross Incurred], FILTER(ALL(InjuryCause[Cause Grouping]), [Top 5 by frequency and Incurred]<6))

VAR _LN = LEN(FORMAT(CALCULATE([Count of Total Gross Incurred], ALL('LossRun'[Reporting Location City])), "text"))

VAR _Pre = _FreqByCity *POWER(10, _LN*2)

VAR _Inc = CALCULATE(RANKX(ALLSELECTED(LossRun[Reporting Location City]), [Sum of Total Gross Incurred],,ASC,Dense), ALLSELECTED(InjuryCause[Cause Grouping]))

VAR _Mid = _Inc * POWER(10, _LN)

RETURN

IF(ISBLANK([Count of Total Gross Incurred]), BLANK(), _Pre + _Mid + RANKX(ALLSELECTED(InjuryCause[Cause Grouping]),[Ref],,ASC,Skip))

This is used to get the Top 5 Injury Causes -

Top 5 by frequency and Incurred =

IF (

    ISBLANK ( [Sum of Total Gross Incurred] ),

    BLANK (),

    RANKX ( ALL ( InjuryCause[Cause Grouping] ), [Ref],, DESC, SKIP )

)

Here are filters used 

Table Ok but Clustered Column Chart is not?

Why does table work and Visual does not?  

[bdehning]

I forgot to include another measure that is used inside both Measures above that may impact your solution.  

Ref =

VAR _MX =

    MAXX (

        ALL ( InjuryCause[Cause Grouping] ),

        CALCULATE ( SUM ( LossRun[Total Gross Incurred] ) )

    )

VAR _LNGTh =

    LEN ( FORMAT ( INT ( _MX ), "Text" ) ) + 1

RETURN

    COUNT ( LossRun[Total Gross Incurred] ) * POWER ( 20, _LNGTh )

        + SUM ( LossRun[Total Gross Incurred] )

[Sahir_Maharaj]

Hello @bdehning,

If ALL or ALLSELECTED is used in RANKX, the filtering behavior in charts can differ, leading to missing or unexpected results. Can you please try this approach:

Ranked Cities = 
VAR _TopCities = 
    ADDCOLUMNS (
        SUMMARIZE (
            LossRun,
            LossRun[Reporting Location City]
        ),
        "@Rank", RANKX (
            ALL(LossRun[Reporting Location City]),
            [Count of Total Gross Incurred],
            ,
            DESC,
            DENSE
        )
    )
RETURN
    IF (
        LOOKUPVALUE ( "@Rank", _TopCities, LossRun[Reporting Location City] ) <= 5,
        [Count of Total Gross Incurred]
    )

Ranked Causes = 
VAR _TopCauses = 
    ADDCOLUMNS (
        SUMMARIZE (
            LossRun,
            InjuryCause[Cause Grouping],
            LossRun[Reporting Location City]
        ),
        "@Rank", RANKX (
            FILTER (
                ALLSELECTED ( InjuryCause[Cause Grouping] ),
                LossRun[Reporting Location City] = SELECTEDVALUE ( LossRun[Reporting Location City] )
            ),
            [Sum of Total Gross Incurred],
            ,
            DESC,
            DENSE
        )
    )
RETURN
    IF (
        LOOKUPVALUE ( "@Rank", _TopCauses, InjuryCause[Cause Grouping], LossRun[Reporting Location City] ) <= 5,
        [Sum of Total Gross Incurred]
    )

[bdehning]

Both Returns on the two measures say the say thing.

Function LOOKUPVALUE expects a column reference as Argument number 1. 

Then "@Rank" - Parameter is not the correct type and Cannot find name "[Reporting Location City]"

Ranked Causes 

It says [Cause Grouping] - Cannot find name "[Cause Grouping]" in addition to what is above in Ranked Cities

Help?