Forum Discussion
Helper IClusters based on the rank
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
4 Replies
- Stachu
Community Champion
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
