topic Re: Clusters based on the rank in DAX Commands and Tips

Clusters based on the rank

Morrison — Sat, 20 Jan 2024 10:41:04 GMT

Hi, in my model I have a table similar to this one below and I would like to create clusters based on the rank I reach.
I created a measure that calculates the Max rank for that sector. Now I would like to divide the sales rank by the sector maxrank and with this value which varies from 0 to 1 I want to create bands. 0 to 0.2 Hig Performer, 0.2 to 0.4 Medium Performer, 0.4 to 0.6 Low Performer, 0.6 to 0.8 Very Low Performer, 0.8 to 1" Not classifiable". The next step is to count how many times store A ranked as High Performer, how many times low performer, etc...
How can I achieve this goal?
Thank you all for the support

Re: Clusters based on the rank

Stachu — Sat, 20 Jan 2024 12:58:57 GMT

in order to present the Group Names in a visual like matrix, you need these entries to be avialable in a table, it cannot be handled only by a measure. I suggest adding following table (let's call it Clusters) to the model:

GroupName	BandStart	BandEnd
HighPerformer	0	0,2
Medium Performer	0,2	0,4
Low Performer	0,4	0,6
Very Low Performer	0,6	0,8
Not Classifiable	0,8	1

without adding any relationships. With that table you can put the Table[Store] and Clusters[GroupName] in the visual, and use measure like this:

StoreInClusterCount = VAR _cluster = SELECTEDVALUE ( 'Clusters'[GroupName] ) VAR _store = SELECTEDVALUE ( 'Table'[Store], BLANK () ) VAR _band_start = MIN ( 'Clusters'[BandStart] ) VAR _band_end = MAX ( 'Clusters'[BandEnd] ) VAR _stores = FILTER ( 'Table', [GroupNumber] >= _band_start && [GroupNumber] < _band_end ) RETURN COUNTROWS ( _stores )

I would also recommend to sort column Clusters[GroupName] by one of the bands column in the table settings, this will force business order over the alphabetic one

Re: Clusters based on the rank

Morrison — Sat, 20 Jan 2024 17:04:09 GMT

Stachu Thanx, i try but the visual is blank.

Re: Clusters based on the rank

Stachu — Sun, 21 Jan 2024 19:49:02 GMT

what's the code for the [GroupNumber] measure? is the table granularity similar to what you posted in the example, are are these aggregated values by Store, Contest and Sector?

Re: Clusters based on the rank

Morrison — Tue, 23 Jan 2024 10:44:39 GMT

My template table looks like this, and I created the 2 measures to calculate the rankmaximum for each sector and the groupnumber

GroupNumber = var vrankstore = max(FactVenduto_Rank[RankVenduto_Comparto])

var vmaxrankcomparto = [RankMax]

return divide(vrankstore,vmaxrankcomparto)

RankMax = calculate( max(FactVenduto_Rank[RankVenduto_Comparto]),all(FactVenduto_Rank[StoreKey]),values(FactVenduto_Rank[Comparto]))

Yes, I confirm that the data is aggregated