cancel
Showing results for
Did you mean:
Highlighted
New Member

## Clustered bar chart, clustered in order of size

Hi,

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

Accepted Solutions
Highlighted Community Support

## Re: Clustered bar chart, clustered in order of size

Hi @benndmint , 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.

2 REPLIES 2
Highlighted Super User IV

## Re: Clustered bar chart, clustered in order of size

@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

My Recent Blog -Week is not so Weak Connect on Linkedin

Proud to be a Super User!

Highlighted Community Support

## Re: Clustered bar chart, clustered in order of size

Hi @benndmint , 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.

Announcements #### Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members. #### Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start. #### Galleries

Looking for inspiration on how to present your data? Need instructional videos? Check out our Galleries! Top Solution Authors
Top Kudoed Authors
Users online (2,027)