Sort by sum

[BI_now]

Hello,

I hope you can help me.

There are a lot of customers with numbers 70009, 70014 and so on. I want to sort by the sum of "Verkauf QM".

But Power Bi is sorting the months as well. The months must be in the right order Jan, Feb, Mar,...

In excel it is easy but in Power Bi it is really frusttrating...

Here one example with the data:

Thanks in avance.

[v-xuding-msft]

Hi @BI_now ,

Please refer to my .pbix file.

[BI_now]

Thank you Xue Ding.

But in your example your lowest data is in january the highest is december.

When you sort "Verkauf QM" descending the december is at the top. But the month must be in the correct order january, february,...

I tried it with month number in the data again but it's still not working.

BUT what is working now is when I sort it with "KundeNummer" and "KundeAnschrift", ascending and descending, the months are in the correct order jan, feb, mar, and so on.

When I sort "Verkauf QM" it's the same problem like before, descending the best month is at the top.

[v-lionel-msft · Accepted solution]

Hi @BI_now ,

You want  the "Verkauf QM" column to be sorted in descending order like this and the months are sorted starting from January, right?

 

It's impossible, the value of "Verkauf QM" corresponding to each month is fixed.

So you either sort the "Verkauf QM" in descending order or sort the month.

Best regards,
Lionel Chen

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

[BI_now]

Hi,

I want to sort by sum of the customers (70009, 70014,... -> there are more than 300 customers)

-> The sum is 10.622.261,13 for 70009 then 6.605.161,42 for 700014 and so on.

But the month "inside" this customer has to be in the correct order Jan, Feb, Mar...

[negi007]

@BI_now When you sorting your data, you get option to sort by row or  values. Below data is default sorted

But when i sort the data by values field, Higher value will come at the top like below and month order will also change

So you can do sorting by either by value field or rows field.

[BI_now]

But this is not the solution isn't it??

You have to do it with more then one total, look at my example.

I want to sort the total (10.622.261, 6.605.161,...) but not the monthly data inside this customer. The months must be in the correct order January, February, March and so on.

I can not believe that this is not possible...

[Anonymous]

Yes i understand You want to sort by totals.

As far as i can see you cant 

[BI_now]

This would be a big weakness in Power BI.

In an excel matrix it is no problem. The totals are sorted but the data inside is sorted correctly by month.

[Anonymous]

That doesnt make sense at all

Your sales of 44k Verkauf QM happened in January

No matter how you sort the sales happened in January

If i understand correctly you would say (when sorting descending) Sales of 83k happened in January. That just is not true

[BI_now]

I do not unerstand what you mean.

The customers with highest "Verkauf QM" have to be at the beginning, sort by sum of "Verkauf QM".

But the sales of the month must be in the correct order Jan, Feb, etc.

Like this (i do it with cutting just for picture):

Do you know what I mean?

[amitchandak]

@BI_now , Seem like data is sorted on measure/value, It should be sorted on Rows columns.

Second, you need sort column for the month 

Month Sort = month([date])

https://docs.microsoft.com/en-us/power-bi/desktop-sort-by-column

[Anonymous]

In you data select the monthname (Jan Feb Etc)

Then go to the Columns Tools Tab and Select Sort By column.

There select the Monthnumber.

For sorting in visuals select the 3 dots (...) and go to sort by column

Go to the 3 dots again and select the sort order 

[BI_now]

The months are correct in the data like you can see here:

But when I sort by "Verkauf QM" the months are sorted as well. I just want to sort by the sum.

[amitchandak]

@Anonymous , if you sort value Verkauf QM, it reorders both sub total and data inside. I doubt that can sort on month after that. If I got it correctly