Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

X-Axis with two levels - Sorting should be different

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

Need Help
  • ppm1's avatar
    ppm1
    3 years ago

    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

     

2 Replies

  • ppm1's avatar
    ppm1
    Icon for Solution Sage rankSolution Sage
    3 years ago

    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

     

  • power-doom's avatar
    power-doom
    Regular Visitor
    2 years ago

    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