Dynamic Ranking with tiers

[cdcphist]

Hi,

I have the following example data:

ID	company	value	tier
1	A	1	tier 1
3	A	5	tier 3
4	A	2	tier 2
8	A	3	tier 2
7	B	2	tier 2

Tier is a calculated column that has the following syntax:

tier = 
SWITCH (
TRUE (),
table[value] < max(table[value]) * 0.25, "tier 1",
table[value] < max(table[value]) * 0.50, "tier 2",
table[value] < max(table[value]) * 0.75, "tier 3",
"tier 4"
)

If i filter my data through slicers then not all rows of my data are being considered, meaning that instead of recalculating tiers for the filtered data, one or more of my tiers go missing. For example, if i use the slicers and choose only 2 companies:

ID	company	value	tier
1	A	1	tier 1
3	A	5	tier 3
4	A	2	tier 2
8	A	3	tier 2
7	B	2	tier 2

I have no tier 4, because the max value is a company that is not selected. I've tried creating a secondary filtered table but i'm having trouble applying the filters through the slicers. How can i make this dynamic in order to always have all the tiers available?

Thanks.

[v-xulin-mstf · Accepted solution]

Hi @cdcphist,

Sorry for delay reply.

I'm not sure your expected output.

Do you want to sort the value under each tier?

Please try calculated column as:

Rank_tier = 
var _tier='Table'[tier]
return
RANKX ( FILTER ( 'Table', 'Table'[tier] =_tier),'Table'[value] )

Best Regards,
Link

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

[cdcphist]

Bump!

[amitchandak]

@cdcphist , Based on what I got, Try a measure like

tier =
var _max = maxX(all(Table), table[value])
SWITCH (
TRUE (),
table[value] < _max * 0.25, "tier 1",
table[value] < _max * 0.50, "tier 2",
table[value] < _max * 0.75, "tier 3",
"tier 4"
)

[cdcphist]

Hi @amitchandak ,

Thank you. I tried your suggestion but i get an error that a single value for "table[value]" cannot be determined.