Sort Graph by 2 Categories

[bernate]

Hello!

I have the following graph. On the x axis I have date and employee name (the black bars are censored names). On the y axis I have sum of hours. 

I would like to sort the graph by date, then by sum of hours descending. It should look like below. I did this by adding sum of hours to the x axis between date and employee name, but now the graph is trying to show hours as well (and the dates are also descending).

Is there a way to sort the graph to show dates on the x axis in ascending order and to sort the bars that represent hours in descending order? I do not need any sort on employee name, but it still needs to be included on the x axis.

[v-sdhruv]

Hi @bernate ,
Just wanted to check if you had the opportunity to review the suggestions provided?
If the response has addressed your query, please accept it as a solution so that other members can easily find it.
Thank You

[v-sdhruv]

Hi @bernate ,
Just wanted to check if you had the opportunity to review the suggestions provided?
If the response has addressed your query, please accept it as a solution so that other members can easily find it.
Thank You

[v-sdhruv]

Hi @bernate ,
Just wanted to check if you had the opportunity to review the suggestions provided?
If the response has addressed your query, please accept it as a solution so that other members can easily find it.
Thank You

[danextian]

Hi @bernate 

Column chart can either be sorted by the value or by category only and not a combination of both so by default  this should be impossible. What you can do is to Rank time according to aggregation used in the visual and materialize this wrank using a disconnected table. Add the dimension from that disconnected table and sort using the categories (that dimension is considered a category).

The calculated table below generates a series of numbers from 1 to 24 (as there only 24 hours a day). The second column with invisible values is what will be used in the visual.

Ranks = 
SELECTCOLUMNS (
    GENERATESERIES ( 1, 24, 1 ),
    "Rank", [Value],
    "Rank2", REPT ( UNICHAR ( 8203 ), [Value] )
)

Create a measure that ranks the time. Note: I am using a separate time dimensions table called Hours.

Time Rank = 
RANKX ( ALL ( Hours ), [Sum of Value],, DESC, DENSE )

Create another measure to assign the materialize Time Rank by assigning the rank value to the Rank column in Ranks table.

Value by Rank = 
SUMX (
    FILTER (
        SUMMARIZECOLUMNS (
            Hours[Hour],
            "@rank", [Time Rank],
            "@value", [Sum of Value]
        ),
        [@rank] IN VALUES ( Ranks[Rank] )
    ),
    [@value]
)

Add Date, Rank2 and Time columns to the visual as well as the measure above. Expand the hierarchy up to the lowest level. Sort by category in ascending order.

Please see the attached pbix.

[Ashish_Mathur]

Hi,

I am not sure how much i can help but i would like to try.  Share the download link of the PBI file.

[Irwan]

hello @bernate 

to manage multiple value sort, i usually make another calculated column for sorting such as using CONCATENATE then put the new calculated column as tooltip value and use it as sort value.

i used CONCATENATE between x-axis and y-axis column value below.

you can CONCATENATE between date and y-axis as sort value, so it will group for same date as shown below.

Hope this will help.

Thank you.

[bernate]

Hi Irwan, unfortunately this did not work because my sum of hours per employee is not just one row. Some employees have multiple rows of hours listed per day. I could group by hours on employee name and date, but I have many other columns in the table where the values are different per row.