X-Axis with two levels - Sorting should be different

[AnSc93]

Hi together,

I have a column chart and two levels on the X-Axis.

The Axis aren't concatenated and both levels are always shown.

Is it possible, to sort one axis by the value and the other one based on ABC for example?

I can only find a solution which works either the one or the other way.

Thank you and regards,

Andreas

[power-doom]

Just curious;

How would you apply this concept if the x axis of the graph is in a hierarchical order (ie: Stores[City] > Calendar[Year] from a connected datetable), and I want to sort first on City (ASC) and then on year (DESC)?
So not on total value per city per year, but rather just alphabetical (city) and then on year descending.

Kind regards,

Doom

[ppm1 · Accepted solution]

One approach is to create a measure that generates a sort value that considers both the outer and inner dimensions in your visual. You then put the measure in the tooltips area of your visual, click on the ellipsis and sort by it. This example determines the total value at the outer dimension level and does the overall sort; the inner dimension is then sorted reverse alphabetically by name (but you could also use the measure value too).

NewSort =
VAR thisQty = [Total Qty]
VAR thisouter =
    SELECTEDVALUE ( Stores[City] )
VAR thisinner =
    SELECTEDVALUE ( Products[Subcategory] )
VAR outerrank =
    RANKX (
        ALLSELECTED ( Stores[City] ),
        CALCULATE ( [Total Qty], REMOVEFILTERS ( Products[Subcategory] ) )
    )
VAR innerrank =
    RANKX ( ALLSELECTED ( Stores[City] ), Stores[City], thisinner )
RETURN
    IF ( NOT ( ISBLANK ( thisQty ) ), 100 * outerrank + innerrank )

Pat