How to Create a Grouping Based off of Rankings

[Anonymous]

I am trying to create groupings for a waterfall chart based off rankings to make my visual much easier to interpret. What I currently have is below:

I am attempting to make groups for this chart based off a ranking measure that I created for the Service Lines in terms of their Total $:

I attempted to create an IF statement where, if the Service Line rank was 1-4 it would show up as its own Service Line, and if its' rank was 5+ they would all be grouped into one category called "Other", but I could not get it to work. Is there a way to do this?

[Anonymous]

1st step is to create a disconnected table that will have your ranges and values you want to show ( I called it Groupings):

Then this is the measure to bring in the value of Label from the table above.  

Banding = 
CALCULATE( 
    VALUES( Groupings[Label] ) , 
    FILTER ( 
        ALL (Groupings), 
        [Rank] >= Groupings[Start] 
            && [Rank] < Groupings[End]
    )
)

[Rank] above references the measure you are using to get the actual rankings

And the final table:

[Anonymous]

I think this is what I'm looking for other than the fact that the 1-5 will all each have different specific service line names and not just "Service Line". Is that possible?

[v-lili6-msft]

HI, @Anonymous 

There is a same case i have solved for you refer to:

https://community.powerbi.com/t5/Desktop/Top-N-and-Others-monthly/m-p/490708#M228672

https://community.powerbi.com/t5/Desktop/Show-Top-N-and-sum-up-the-Others-as-a-value/m-p/313063#M138849

Hope it could help you.

Best Regards,

Lin

[Anonymous]

The easy way would be just extend the grouping table:

then use this measure:

Banding = 
CALCULATE( 
    VALUES( Groupings[Label] ) , 
    FILTER ( 
        ALL (Groupings), 
        [Rank] > Groupings[Start] 
            && [Rank] <= Groupings[End]
    )
)

[Anonymous]

@Anonymous Sorry, I wasn't clear enough. I meant that I am trying to have this measure dynamic, so I would be able to use it for different clients where the 1-5 service line name could change. I am trying to have those be a measure to where it would equal their service line name and not hard code it into a column. Does that make sense?

[v-lili6-msft]

hi, @Anonymous 

Have you tried the solution what I've offered you, it should meet your requirement.

Best Regards,

Lin

[Anonymous]

@v-lili6-msft It is close to what I am looking for, however my biggest hurdle is the dynamic categories on the x-axis that your solution for the other problem doesn't really address

[Anonymous]

@v-lili6-msft this is my vision for what the chart would look like as a finished product (I manually created it without a measure just as an example)

 

The measure I am looking for would be able to change which categories show up on the x-axis depending on the different rankings for different clients.

[v-lili6-msft]

hi, @Anonymous 

Could you please tell me if your problem has been solved? If it is, could you please mark the helpful replies as Answered?

Best Regards,

Lin

[v-lili6-msft]

hi, @Anonymous 

This is dynamic by the RANKX measure

You could use ALLSELECTED instead of ALL in the rankx measure

For example:

Ctry Rank = RANKX(ALL('Country with others'[Country Name]), 'Dim - Country'[Total])

Ctry Rank = RANKX(ALLSELECTED('Country with others'[Country Name]), 'Dim - Country'[Total])

If not your case, please share the sample data and with the expected output in different scenarios.

Best Regards,

Lin

[Anonymous]

It does, but those dynamic values have to come from somewhere. Do you have a table with them or something similiar. There has to something that relates a specific client to the row you want to bring in.