Sorting categories/legend in stacked bar chart based on measure

[z-eitgeist]

Hey community,

I'm a little desperate after doing a lot of research on my issue but still didn't come up with a proper solution.

I hope you have some ideas on how to solve it.

Here's the challenge:

I'm showing yearly volumes per product models in stacked bar chart. The stacking order of the product models is crucial and I want to simulate different scenarios with different stacking orders. These scenarios are in a separate table.

Now I want the user to select a sorting scenario in the slicer. When changing the selection in the slicer, the sorting order in the bar chart should change according to the information in the table for the selected scenario.

But I have no clue how to implement it.

I think I can capture the selected scenario in the slicer in measure by using "selectedvalue". As it is shown in the card.

But how can I pull this measure for sorting?

For customized sorting I alway used calculated columns. But how to get the measure information in a column? My understanding was that calculated columns don't get updated by user actions.

I hope the given information help to get my problem and thanks for your support.

[AlB · Accepted solution]

Hi @z-eitgeist 

hmm... tricky

The only thing I can think of now is to create several columns with Model1, Model2..., sorted by the corresponding sorting column and create one page per scenario, each using one of those columns in the legend. Then you can navigate from one page scenario to the other. In the service the user will see one page only. It's a clumsy solution/workaround though. Hopefully not much on your data model depends on the Model column. If it is used in any relationship it will become trickier.

Sharing a mock pbix with the basics of what you are trying to do would help 

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[AlB]

perhaps  @MFelix  @TomMartens  will have other ideas

[MFelix · Accepted solution]

Hello @z-eitgeist,

What I think you can do is create two additional columns with the following data (or change your data)

Scenario + Model = Scenarios[Model] & " (" &Scenarios[Scenario_ID] &")"

Scenario + Index = Scenarios[Scenario_ID] & Scenarios[Sort_index]

Now sort the Scenario + Model column by the Scenario + Index column. What I'm creating is a column with unique values that identifies the model and adds a model in square brackets.

Create a table with a unique model ID and use that table to ensure that the relationship between the two tables ensures that bidirectionality is enabled

Now use the Scenario + Model column as your legend, Result below, and attach PBIX.

All you have to do is make a manual color selection for each model/combination scenario as using a legend does not allow condittional formatting.

[z-eitgeist]

Hello @MFelix ,

wow, that's awesome. Thanks a lot!

That's a smart solution. My ideas have been in a totally different direction and I would have never expected it to be so easy.

Thanks again!

I will implement it like this.

[AlB]

@z-eitgeist 

Don't assume... 🙂

Can you by any chance share the pbix? Or a dummy pbix with the basis model for what you are trying to do? I had another idea but need to run a couple of tests and don't have time to build the model myself.

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[z-eitgeist]

Hello @AlB,

would be awesome if you have an alternative idea.

For any reasons I can't attach files in the forum. Please follow to link to find the mock-up file: https://www.dropbox.com/s/9kukr4rem0off57/example-SortingBySlicer.pbix?dl=0

I hope it helps.

[AlB · Accepted solution]

Hi @z-eitgeist 

hmm... tricky

The only thing I can think of now is to create several columns with Model1, Model2..., sorted by the corresponding sorting column and create one page per scenario, each using one of those columns in the legend. Then you can navigate from one page scenario to the other. In the service the user will see one page only. It's a clumsy solution/workaround though. Hopefully not much on your data model depends on the Model column. If it is used in any relationship it will become trickier.

Sharing a mock pbix with the basics of what you are trying to do would help 

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[z-eitgeist]

Hey @AlB ,

thanks for your feedback. 

Your proposal would work and I already assumed there wouldn't be an elegant solution. 
But I will try. 🙂