Bar chart sort by volume but "others" at the bottom

[Mayu]

I am looking for a way to sort a bar chart based on the volume but "Others" keep at the bottom. 

It seems like I can only sort by the volume or category name in alphabetical order. Is there a way?

[Mayu]

Thank you, v-zhangti.

I needed to tweak slightly because rankx was not bringing the value I was expecting but I was able to make it work! Thank you for the help! Greatly appreciated!!

Sort 2 =

Var _Rank=RANKX(FILTER(ALL(v_salesforce_feedback[Complaint Category],v_salesforce_feedback[record_type_name__c]),[record_type_name__c] = "Complaint" && [Complaint Category]<>"Other"),[Ttl Complaint # by Category],,DESC)

Var _Maxcount=CALCULATE(distinctCOUNT(v_salesforce_feedback[Complaint Category]), filter(all(v_salesforce_feedback),[record_type_name__c] = "Complaint"))

RETURN

IF(SELECTEDVALUE(v_salesforce_feedback[record_type_name__c])<>"Complaint",blank(), if(SELECTEDVALUE(v_salesforce_feedback[Complaint Category]) <> "Other",_Rank,_Maxcount))

Mayu

[v-zhangti]

Hi, @Mayu 

You can try the following methods.

Example data:

Measure:

Sum Measure = CALCULATE(SUM('Table'[Value]),ALLEXCEPT('Table','Table'[Category]))

Sort = 
Var _Rank=RANKX(FILTER(ALL('Table'),[Category]<>"Other"),[Sum Measure],,DESC)
Var _Maxcount=CALCULATE(DISTINCTCOUNT('Table'[Category]),ALL('Table'))
Return
IF(SELECTEDVALUE('Table'[Category])<>"Other",_Rank,_Maxcount)

Is this the result you expect? Please see the attached document.

Best Regards,

Community Support Team _Charlotte

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

[Mayu]

Thank you so much, Charlotte!

I was able to get up to here but the sorting number was not as I expected. I need to filter the value by record_type_name__c = "Complaint".  Could you help me where I can insert the filiter logic? 

Thank you!

I need to filter record name 

[v-zhangti]

Hi, @Mayu 

Can you give an example of what the relationship between Category and record name looks like?

Best Regards

[Mayu]

Thank you, v-zhangti

record_type_name is the highest level that the inquery is sorted out like below.  Of those, complaint category are designed to get more details so there are complaint category and more. 

Complaint category is one below level from record_type_name. I want to assign the sorting number within the Complaint category (Complaint Category = if(v_salesforce_feedback[complaint_category__c] = "" && v_salesforce_feedback[record_type_name__c] = "Complaint", "Other",
if(v_salesforce_feedback[record_type_name__c] = "Complaint" && v_salesforce_feedback[complaint_category__c] <> "", v_salesforce_feedback[complaint_category__c] , v_salesforce_feedback[record_type_name__c])).

I tried the workaround to enter -99999 and added to Tooltips. it works but -99999 is showing up and it might confuse the dashboard users. So if I can make the Sorting function works, that would be the best solution for us. 

Thank you for your help!

[v-zhangti · Accepted solution]

Hi, @Mayu 

You can try the following methods.
Example data:

Sum Measure = CALCULATE(SUM('Table'[Value]),ALLEXCEPT('Table','Table'[Category],'Table'[record_type_name__c]))

Sort1 = 
Var _Rank=RANKX(FILTER(ALL('Table'),[Category]<>"Other"),[Sum Measure],,DESC)
Var _Maxcount=CALCULATE(DISTINCTCOUNT('Table'[Category]),ALL('Table'))
Return
IF(SELECTEDVALUE('Table'[Category])<>"Other",_Rank,_Maxcount)

Sort2 = 
Var _Rank=RANKX(FILTER(ALL('Table'),[Category]<>"Other"&&[record_type_name__c]="Complaint"),[Sum Measure],,DESC)
Var _Maxcount=CALCULATE(DISTINCTCOUNT('Table'[Category]),FILTER(ALL('Table'),[record_type_name__c]="Complaint"))
Return
IF(SELECTEDVALUE('Table'[record_type_name__c])<>"Complaint",BLANK(),IF(SELECTEDVALUE('Table'[Category])<>"Other",_Rank,_Maxcount))

Which is your desired sorting result? If this does not solve your problem, please provide more information.

Best Regards,

Community Support Team _Charlotte

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

[vicky_]

There's two work-arounds that I found:
The first one is from this forum thread - https://community.fabric.microsoft.com/t5/Desktop/Sort-Stacked-Bar-chart/m-p/3418133 where you basically put a custom sort in your tooltips, and sort by that.

Or you'll need to create a cusom sort measure, which is explained at the end of this article: https://www.sqlbi.com/articles/filtering-the-top-products-alongside-the-other-products-in-power-bi/