Forum Discussion

bdehning's avatar
bdehning
Post Prodigy
1 year ago

Rankx TOP 5 Value Help

 

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?  

 

Need Help

3 Replies

  • Sahir_Maharaj's avatar
    Sahir_Maharaj
    Super User
    1 year ago

    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's avatar
      bdehning
      Post Prodigy
      1 year ago

      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?
  • bdehning's avatar
    bdehning
    Post Prodigy
    1 year ago

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