Forum Discussion
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 a...
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]
)
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?