topic Re: Bar chart sort by volume but "others" at the bottom in DAX Commands and Tips

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

Anonymous — Mon, 11 Dec 2023 22:33:07 GMT

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?

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

vicky_ — Mon, 11 Dec 2023 23:52:02 GMT

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/

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

v-zhangti — Wed, 13 Dec 2023 08:15:53 GMT

Hi, Anonymous

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.

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

Anonymous — Wed, 13 Dec 2023 18:22:25 GMT

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

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

v-zhangti — Thu, 14 Dec 2023 01:34:43 GMT

Hi, Anonymous

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

Best Regards

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

Anonymous — Thu, 14 Dec 2023 19:50:10 GMT

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!

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

v-zhangti — Fri, 15 Dec 2023 08:30:17 GMT

Hi, Anonymous

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.

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

Anonymous — Tue, 19 Dec 2023 19:20:50 GMT

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