locked
Sorting in Power BI Desktop RRS feed

  • Question

  • Hi

    How to properly sort columns in this chart?

    Should be:

    <3, 3-4, 5-6, ..... , >20

    Chart shows sales by price tiers. Price tiers are  dynamics, based on calculated column.

    May change bottom border, top border and range. 

    Any idea?

    Thursday, March 3, 2016 6:18 PM

Answers

  • Hi Bartek,

    Teylyn is right that this thread belongs on http://community.powerbi.com/, although I can appreciate that you may have posted here because Power Pivot and Power BI Desktop share the same DAX language.

    In the future, please post questions relating specifically to Power BI Desktop in the Power BI community forum.

    That said, if I've understood your requirement correctly (I've made some assumptions about your data), something along these lines would work for your calculated column:

    Sorting Column = 
    SWITCH(
      TRUE(), 
      FIND("<", Data[PRICE TIER],,0) <> 0, 1, //Always give the number with '<' character a sort/rank of 1 
      FIND(">", Data[PRICE TIER],,0) <> 0, COUNTROWS(ALL(Data[PRICE TIER])), //Always give the number with the '>' character a sort/rank of the last position 
      RANKX(
        FILTER(
          ALL(Data[PRICE TIER]),
          FIND("<", Data[PRICE TIER],,0) = 0 
          && FIND(">", Data[PRICE TIER],,0) = 0
        ), 
      Data[PRICE TIER],,ASC) + 1 //Derive the rank of the tiers in between and shift them to start at 2
    )


    Regards,

    Michael Amadi

    Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to vote it as helpful :)

    Website: http://www.nimblelearn.com, Twitter: @nimblelearn

    • Marked as answer by Charlie Liao Friday, April 8, 2016 10:10 AM
    • Edited by Michael Amadi Friday, April 8, 2016 5:08 PM Minor edit
    Wednesday, April 6, 2016 6:36 PM

All replies

  • Hi,

      You may have to add a new column to specify this values needs to be sorted based on the sorting column. See the below link for more details

    https://powerbi.microsoft.com/en-us/documentation/powerbi-desktop-sort-by-column/


    Best Regards Sorna

    Thursday, March 3, 2016 7:25 PM
  • Thanks

    But this article shows general approach. It assumes sorting column exist in data model.

    In my case this column does not exist.

    I have the one looking like this:

    PRICE TIER:
    13-14
    14-15
    13-14
    <3
    16-17
    16-17
    >20

    To use "Sort by Column" option I need Sorting Column like the one below:

    PRICE TIER: Sorting Column
    13-14 2
    14-15 3
    13-14 2
    <3 1
    16-17 4
    16-17 4
    >20 5

    Any Ideas how this "Sorting Column" syntax should look like?

    The sign "<" always open ranking as well as ">" always close it


    Thursday, March 3, 2016 10:57 PM
  • Add a new table with the unique price tiers and the sorting column.

    Create a relationship between the two tables on the price tier column.  Make the sorting column the sort column for price tier.

    Drag the price tier from the related table into the Axis well of the column chart and sort. It will now be sorted according to the sorting column. 



    Friday, March 4, 2016 12:49 AM
  • Guys

    It is all clear but I need a formula to do the sorting column.

    My PRICE TIER calculated column formula...

    PRICE TIER =
    IF (
        'SKIN CARE'[AVG PRICE (PLN)] < 'SKIN CARE'[BOTTOM BORDER];
        "<" & 'SKIN CARE'[BOTTOM BORDER];
        IF (
            'SKIN CARE'[AVG PRICE (PLN)] >= 'SKIN CARE'[TOP BORDER];
            ">" & 'SKIN CARE'[TOP BORDER];
            'SKIN CARE'[PRICE TIER (MIN)] & "-"
                & 'SKIN CARE'[PRICE TIER (MAX)]
        )
    )

    ... is dynamic. It depends on others columns. Top and bottom borders can be modified as well as tier range.

    For instance:

    3-4  ->  3-5

    3-4  -> 3-6

    <4   ->  <3

    So sorting column can not be fixed. I know I need this but I have no idea how to write a DAX for this.

    Even if I create a new related table the sorting column is still necessary 


    Friday, March 4, 2016 10:28 AM
  • You don't need the sorting column in your data table. As I showed in my post above, you can do this with a related table. 

    That way you don't need to have the sort column in the main data source.

    Is that not working for you?


    cheers, teylyn

    Sunday, March 6, 2016 12:20 AM
  • Excel 2010 with free Power Query Add-In.
    Compatible with Office 2013/2016 Pro Plus.
    Generate Sorting Column with PQ.
    PQ dynamically integrated with PP in 2013/2016.
    http://www.mediafire.com/download/fwx3m2ri1wfs6cp/03_06_16a.xlsx
    http://www.mediafire.com/download/6gkz9qphf5wrdpq/03_06_16a.pdf

    Monday, March 7, 2016 2:11 AM
  • Moderators, this question started out in the Power BI forum. It is about Power BI and it belongs in that forum. Whoever moved it to the SQL Server > Power Pivot forum: Please move it back and learn a bit more about Power BI.

    cheers, teylyn

    Tuesday, March 8, 2016 8:37 PM
  • Any feedback on this?

    cheers, teylyn

    Tuesday, April 5, 2016 12:17 AM
  • Bartek,

    What teylyn is suggesting is correct, if I were you, just use the "Enter Data" in Power BI Desktop to enter your unique pricing tiers and sorting. Basically, you are just creating a table for these by entering data manually. You then relate the two tables and choose the Sort By column.

    Tuesday, April 5, 2016 4:50 PM
  • Hi Bartek,

    Teylyn is right that this thread belongs on http://community.powerbi.com/, although I can appreciate that you may have posted here because Power Pivot and Power BI Desktop share the same DAX language.

    In the future, please post questions relating specifically to Power BI Desktop in the Power BI community forum.

    That said, if I've understood your requirement correctly (I've made some assumptions about your data), something along these lines would work for your calculated column:

    Sorting Column = 
    SWITCH(
      TRUE(), 
      FIND("<", Data[PRICE TIER],,0) <> 0, 1, //Always give the number with '<' character a sort/rank of 1 
      FIND(">", Data[PRICE TIER],,0) <> 0, COUNTROWS(ALL(Data[PRICE TIER])), //Always give the number with the '>' character a sort/rank of the last position 
      RANKX(
        FILTER(
          ALL(Data[PRICE TIER]),
          FIND("<", Data[PRICE TIER],,0) = 0 
          && FIND(">", Data[PRICE TIER],,0) = 0
        ), 
      Data[PRICE TIER],,ASC) + 1 //Derive the rank of the tiers in between and shift them to start at 2
    )


    Regards,

    Michael Amadi

    Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to vote it as helpful :)

    Website: http://www.nimblelearn.com, Twitter: @nimblelearn

    • Marked as answer by Charlie Liao Friday, April 8, 2016 10:10 AM
    • Edited by Michael Amadi Friday, April 8, 2016 5:08 PM Minor edit
    Wednesday, April 6, 2016 6:36 PM