Forum Discussion

BI_now's avatar
BI_now
Regular Visitor
5 years ago
Solved

Sort by sum

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.

Need Help
  • v-lionel-msft's avatar
    v-lionel-msft
    5 years ago

    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.

14 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    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's avatar
      BI_now
      Regular Visitor
      5 years ago

      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's avatar
        amitchandak
        Super User
        5 years ago

        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

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    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's avatar
      BI_now
      Regular Visitor
      5 years ago

      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?

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Yes i understand You want to sort by totals.

    As far as i can see you cant 

    • BI_now's avatar
      BI_now
      Regular Visitor
      5 years ago

      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.

  • negi007's avatar
    negi007
    Community Champion
    5 years ago

    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's avatar
      BI_now
      Regular Visitor
      5 years ago

      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...

    • BI_now's avatar
      BI_now
      Regular Visitor
      5 years ago

      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's avatar
        v-lionel-msft
        Community Support
        5 years ago

        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.