Cluster Bar Chart - Variance - Sort by Absolute

[pbernardy]

Hey everyone,

I am trying to set up a dashboard for daily sales tracking.

As part of it, I want a clustered bar chart with the sales variance vs forecast/prior year by customer.

The problem is I got positive and negative figures and I got hundreds of customers. So I want the bar chart to be sorted by the absolute variance, so that I have the customers on top that shows the biggest variance (irrespective if it's positive or negative)

I already calcualted the "absolute variance" as a separate measure, but couldn't find a way to sort the chart in the way without adding the "absolute variance" in the visual as a second value on the x-axis.

Directionally the data looks like sth like this.

So I got the variance column (Sales_Var_vs_Fcst), the customer (Name), so the standard order of the visual would be like in the column (Order by Value). However, I would like to have it ordered by the target order column (Target Order), which is based on the absolute variance (Absolute)

Sales_Var_vs_Fcst	Name	Order By Value	Absolute	TARGET ORDER
315,319	Customer 1	1	315,319	2
176,220	Customer 2	2	176,220	5
154,008	Customer 3	3	154,008	6
152,948	Customer 4	4	152,948	7
136,824	Customer 5	5	136,824	8
133,069	Customer 6	6	133,069	9
52,289	Customer 17	7	52,289	11
52,232	Customer 18	8	52,232	12
46,580	Customer 19	9	46,580	13
42,980	Customer 20	10	42,980	14
-                                   100,000	Customer 124	11	100,000	10
-                                   200,000	Customer 125	12	200,000	4
-                                   250,000	Customer 126	13	250,000	3
-                             10,000,000	Customer 127	14	10,000,000	1

thanks for your help

[Anonymous · Accepted solution]

Hi @pbernardy ,

I create a table as you mentioned.

Then I think you can create three measures and here are the DAX code.

Order By Value = 
RANKX (
    ALL ( 'Table' ),
    CALCULATE ( SUM ( 'Table'[Sales_Var_vs_Fcst] ) ),
    ,
    DESC,
    DENSE
)

Absolute =
IF (
    MAX ( 'Table'[Sales_Var_vs_Fcst] ) < 0,
    ABS ( MAX ( 'Table'[Sales_Var_vs_Fcst] ) ),
    MAX ( 'Table'[Sales_Var_vs_Fcst] )
)

Measure = 
RANKX(ALL('Table'), CALCULATE('Table'[Absolute]), , DESC, Dense)

Best Regards

Yilong Zhou

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

[amitchandak]

@pbernardy , Create a measure and add that to the tooltip and now you can sort on that column,

If this does not help, please provide data and expected output as suggested by @Tahreem24 

[Tahreem24]

@pbernardy  Kindly explain your scenario with the help of sample dummy data.