How are clustered column charts ordered when the cluster is based on the same column?

[ABNHS84]

Hi All,

I'm curious, how is the order of clustered column charts decided? In the below chart (sorry for trimming off the legend/axis but it's sensitive information) all the values are coming from the column 'Count', with the clusters based on the value in the adjacent column (Variable). I have it ordered descending by the 'Count' column. It must use some logic to decide which cluster goes first. I've done some quick calculations and it's not the sum of the values or the average of the values, so what is it based on?



 

Any insight on this would be gratefully appreciated.

Cheers,

Andy

[MFelix]

Hi @ABNHS84,

The Clusters are defined by the sorting order you have on your column that you use to do the cluster (field in the legend of the visual) if the field is text it from A to Z. If you want to change this you can setup a separate table with the sorting and related to your column and then used this to make the legend on your chart.

I have created a simple file two table one is composed by Cat, Group and Value and the other By Cat and Sort

On the second Table I have sorted the CAT by the column sort. If you look at the two charts below you can see that although the cat values are the same the second one as the cluster on a different order this is because the column I used is the one from the second table where I force the sorting as I want and not from A to Z.

Check the PBIX file attach. 

[ABNHS84]

Hi,
Thanks for your response, but I was talking about how the order of each whole cluster is decided, not the bars within a single cluster. I managed to use a sort table joined to my data table with the help of other forum threads :),  

My question really is why is Cluster1 to the left of Cluster2 when it is in descending order? 

+----------+----------+--+
| Cluster1 | Cluster2 |  |
+----------+----------+--+
|      670 |      760 |  |
|      490 |      420 |  |
|      580 |      550 |  |
|      275 |      140 |  |
+----------+----------+--+

[MFelix]

Hi @ABNHS84,

That you cab rearrange, if you select the 3 dots on the the chart and select the sort option you can select the descending or ascending based on values or cluster valie.

Regards,
MFelix

[ABNHS84]

Thank again MFelix, but I think you may be misunderstanding my question; As I mentioned in my original post, I have already ordered my chart by the 'Count' field. However, all my individual bars come from the same column, so I want to know how PBI decides the position of each cluster, when it's based on four different values.

My data looks like this:

+----------+-------+
| Variable | Count |
+----------+-------+
|        1 |   670 |
|        2 |   490 |
|        3 |   580 |
|        4 |   275 |
|        1 |   760 |
|        2 |   420 |
|        3 |   550 |
|        4 |   140 |
+----------+-------+

I have set the legend to the Variable field (which splits my data into the four bars in each cluster), and then I have ordered the chart by 'Count' descending, Which means my chart generally looks like the clusters are descending. But in the image above, only two of the bars in the left hand cluster are higher than their counterpart bars in the right cluster, with the difference between them smaller than the difference of the other bars.

I want to know how the decision was made by PBI to put the left hand cluster on the left.

[OwenAuger · Accepted solution]

@ABNHS84

When sorting your clustered column chart by 'Count', the Clusters are sorted in the same order that they would be sorted if there were no Legend field provided.

In other words, Power BI calculates the measure for each cluster ignoring the Legend field (using whatever aggregation you have selected or an explicit DAX measure), and uses the resulting values to determine the sort order of the Clusters.

You can verify this by removing the Legend field and confirming that the order of the Cluster field on the axis remains the same.

Using the figures you posted, if you are aggregating Count by SUM, the sort order makes sense to me since

Count for Cluster1 = 2,015 > Count for Cluster2 = 1,870.

Just thinking about it: you could control the sort order more finely by coming up with a measure that returns the usual value if the Legend field is filtered, but does something different at an aggregate level.

Regards,

Owen

[ABNHS84]

Ahhh,

Thank you!
Due to an error in my own summing, I thought the total for cluster2 was 2370 (serves me right for doing it on paper), so I discounted it being ordered by sum.

Cheers!