Sort based on Total values in stacked bar chart

[karthik77700]

I have a requirement in which sorting has to be done based on total values of two columns used in stacked bar chart.

Below screenshot we have two direct amount columns in X axis requirement is to sort based on Total amount

Example:110 bn ,50bn,20 bn,14 bn,7bn should be sorted based on Total of two columns

Any help appreciated

[karthik77700]

It worked Thanks for your support 

[karthik77700]

I havent created any measure they are just two direct columns(Ret Amount and Ced amount) used in X axis and there is no legends added as well.Just enabled legends in format field .how to write expression for this

[Nasif_Azam]

Hey @karthik77700 ,

To address the issue of sorting the stacked bar chart based on the total of the two columns (Ret Amount and Ced Amount). Here's the upadated solution:

1. Create a Measure for Total: Since there are no measures currently, you can create a new measure that sums the two columns (Ret Amount and Ced Amount).

   TotalAmount = SUM('YourTable'[Ret Amount]) + SUM('YourTable'[Ced Amount])

2. Sort by the Total Measure: Once you've created this measure, use it to sort the categories in your stacked bar chart. In Power BI, you can:

   • Select your visual.

   • Go to the Fields pane.

   • Select the axis (X-axis in your case).

   • Under Sort by, select the newly created TotalAmount measure.

If you found this solution helpful, please consider accepting it and giving it a kudos (Like) it’s greatly appreciated and helps others find the solution more easily.

Best Regards,
Nasif Azam

[v-aatheeque]

Hi @karthik77700 ,
I wanted to follow up on your requirement regarding sorting the stacked bar chart based on the total values of the two columns.

As mentioned by   community member, the solution provided should help you achieve the desired sorting of your data (e.g., 110 bn, 50 bn, 20 bn, 14 bn, 7 bn) based on the total amounts.If this solution has resolved your issue, please consider marking it as the Accepted Answer. This not only helps acknowledge the effort of the contributor but also assists others in the community who may have similar questions.

If you still have any questions or need further assistance, please feel free to reach out. We’re here to help!

Thank you for being an active part of our community!

Best regards,
atheeq.

[mark_endicott · Accepted solution]

@karthik77700 - so in this case you need to create a DAX measure for the total, this will be:

SUM ( 'Table'[Ret Amount] ) + SUM ( 'Table'[Ced Amount] )

Just replace the word Table with your table name. Then add this to the tooltip as I showed in my screenshot.

If I answered your question please mark my post as the solution, it helps others with the same challenge find the answer!

[Nasif_Azam]

Hey @karthik77700 ,

It seems that you're working on a stacked bar chart and need to sort the bars based on the total of two columns (i.e., the sum of the values on the bar). You can achieve this in Power BI by following these steps:

1. Create a Measure for Total:
   Instead of just summing the two columns, you can create a measure that removes the filters from the legend (or any category you're using in the stacked bar chart) to get the total value. Use the following DAX expression:

   Total Measure = CALCULATE(
       [Your Measure], 
       REMOVEFILTERS('Table'[ColumnInLegend])
   )

   • [Your Measure] would be the measure you're currently using in the visual (for example, a sum of a column).

   • 'Table'[ColumnInLegend] is the column used in the legend or category for your stacked bar chart. This ensures that the total is calculated across all the categories, not just for the ones displayed.

2. Sort by the Total Measure:
   Once you’ve added the Total Measure to the Tooltip, you can use it for sorting your stacked bar chart.

   • Right-click on the axis field in the visual and choose Sort by Total Measure.

If you found this solution helpful, please consider accepting it and giving it a kudos (Like) it’s greatly appreciated and helps others find the solution more easily.

Best Regards,
Nasif Azam

[mark_endicott]

@karthik77700 - create a measure for the total, something like:

CALCULATE ( [Measure in visual], REMOVEFILTERS( 'table'[column in legend] ) ) 

Add this to the tooltip, and then you'll be able to use it in the sort options. 

If I answered your question please mark my post as the solution, it helps others with the same challenge find the answer!