Clustered bar chart, clustered in order of size

[benndmint]

Hi,

Is there a way to order each of the clusters by size for each month?

[Icey · Accepted solution]

Hi @benndmint ,

Please check if this could meet your requirements.

1. Enter data to create a Rank Table.

2. Create measures.

Measure = 
VAR A_ =
    CALCULATE ( SUM ( 'Table'[Value] ), 'Table'[Category] = "A" )
VAR B_ =
    CALCULATE ( SUM ( 'Table'[Value] ), 'Table'[Category] = "B" )
VAR C_ =
    CALCULATE ( SUM ( 'Table'[Value] ), 'Table'[Category] = "C" )
VAR D_ =
    CALCULATE ( SUM ( 'Table'[Value] ), 'Table'[Category] = "D" )
VAR M1 =
    UNION (
        ROW ( "Value", A_, "Category", "A" ),
        ROW ( "Value", B_, "Category", "B" ),
        ROW ( "Value", C_, "Category", "C" ),
        ROW ( "Value", D_, "Category", "D" )
    )
VAR Result1 =
    MAXX ( M1, [Value] )
VAR Result2 =
    MINX ( TOPN ( 2, M1, [Value] ), [Value] )
VAR Result3 =
    MINX ( TOPN ( 3, M1, [Value] ), [Value] )
VAR Result4 =
    MINX ( M1, [Value] )
RETURN
    SWITCH (
        MAX ( 'Rank Table'[Rank] ),
        "1st", Result1,
        "2nd", Result2,
        "3rd", Result3,
        "4th", Result4
    )

Measure Tooltip = 
VAR A_ =
    CALCULATE ( SUM ( 'Table'[Value] ), 'Table'[Category] = "A" )
VAR B_ =
    CALCULATE ( SUM ( 'Table'[Value] ), 'Table'[Category] = "B" )
VAR C_ =
    CALCULATE ( SUM ( 'Table'[Value] ), 'Table'[Category] = "C" )
VAR D_ =
    CALCULATE ( SUM ( 'Table'[Value] ), 'Table'[Category] = "D" )
VAR M1 =
    UNION (
        ROW ( "Value", A_, "Category", "A" ),
        ROW ( "Value", B_, "Category", "B" ),
        ROW ( "Value", C_, "Category", "C" ),
        ROW ( "Value", D_, "Category", "D" )
    )
VAR Result1 =
    MAXX ( M1, [Value] )
VAR Result2 =
    MINX ( TOPN ( 2, M1, [Value] ), [Value] )
VAR Result3 =
    MINX ( TOPN ( 3, M1, [Value] ), [Value] )
VAR Result4 =
    MINX ( M1, [Value] )
VAR Tooltip1 =
    MAXX ( FILTER ( M1, [Value] = Result1 ), [Category] )
VAR Tooltip2 =
    MAXX ( FILTER ( M1, [Value] = Result2 ), [Category] )
VAR Tooltip3 =
    MAXX ( FILTER ( M1, [Value] = Result3 ), [Category] )
VAR Tooltip4 =
    MAXX ( FILTER ( M1, [Value] = Result4 ), [Category] )
RETURN
    SWITCH (
        MAX ( 'Rank Table'[Rank] ),
        "1st", Tooltip1,
        "2nd", Tooltip2,
        "3rd", Tooltip3,
        "4th", Tooltip4
    )

3. Create a Clustered column chart like so:

Then, you can get this:

BTW, .pbix file attached.

Best Regards,

Icey

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

[amitchandak]

@benndmint , I did see any option to to do that. Even tried sort on value measure . but seem like internal  sorting there is no option