Sort 100% stacked bar chart by percentage descending order

[jj304]

Hi, I have a  power bi report using direct query that has a 100% stacked bar chart that displays the volume by users split by category.

The columns are

• Y-axis - User 
• X-axis - Sum of volume
• Legend - Category

 I would like to sort the bars to display by percentage in descending order but currently it always sorts by the legend.  Any ideas how I can force it to sort by % volume.  Thanks

[v-linhuizh-msft]

Thanks for the replies from parry2k.

Hi @jj304 ,

According to your description, I created simple data.

When sorting this chart, it can only be sorted by one of the X-axis or Y-axis, if at the same time, you need the percentage to show the effect of descending, you can create a new calculated column that meets the descending order of the percentage, and use it as the Y-axis. The steps are as follows:

1. Create calculated columns:

AdjustedUser = IF('Table'[Category]="A",RANKX(FILTER('Table','Table'[Category]="A"),'Table'[Volume],,ASC,Dense)&"_"&'Table'[User])

NewUser = 
CALCULATE(MAX('Table'[AdjustedUser]),ALLEXCEPT('Table','Table'[User]))

2. Use Newuser as the y-axis and sort by it:

3. Comparison of results:

Best Regards,
Zhu

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.

[jj304]

Hi @v-linhuizh-msft, thanks for your reply.  I am using Direct Query for this report so i cannot use this dax in a calculated columns.  When i try to use the dax in a measure it forces me to use some form of aggregration for the columns. 

[v-linhuizh-msft · Accepted solution]

Hi @jj304 ,

Since we can't use measure for y-axis, to achieve this sorting effect, I suggest you create a calculated table:

NewTable = ADDCOLUMNS(
    'Table',
    "AdjustedUser1", IF(
        'Table'[Category] = "A",
        RANKX(
            FILTER('Table', 'Table'[Category] = "A"),
            'Table'[Volume],
            ,
            ASC,
            DENSE
        ) & "_" & 'Table'[User]
    ),
    "NewUser1", CALCULATE(
        MAX('Table'[AdjustedUser1]),
        ALLEXCEPT('Table', 'Table'[User])
    )
)

Then use the columns in the new table to create a bar chart.

Best Regards,
Zhu

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.

[parry2k]

@jj304 check the sort column by clicking the 3 dots on the visual.

[parry2k]

@jj304 on the format pane, under layout, turn reverse order on and that will do it.

[jj304]

Hi @parry2k, thanks for your reply.  The reverse order reverses the ordering of bars but its still displaying by the legend (category)