cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
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?

 

Capture.PNG

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Support
Community Support

Re: Clustered bar chart, clustered in order of size

Hi @benndmint ,

 

Please check if this could meet your requirements.

rank.jpg

 

1. Enter data to create a Rank Table.

ranktable.PNG

 

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:

bar.PNG

 

Then, you can get this:

bar chart.gif

 

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.

View solution in original post

2 REPLIES 2
Highlighted
Super User IV
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



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
My Recent Blog -Week is not so Weak Connect on Linkedin

Proud to be a Super User!

Highlighted
Community Support
Community Support

Re: Clustered bar chart, clustered in order of size

Hi @benndmint ,

 

Please check if this could meet your requirements.

rank.jpg

 

1. Enter data to create a Rank Table.

ranktable.PNG

 

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:

bar.PNG

 

Then, you can get this:

bar chart.gif

 

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.

View solution in original post

Helpful resources

Announcements
May 2020 Community Highlights

May 2020 Community Highlights

It’s time for another PBI Community recap!

Community Blog

Community Blog

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

Using the Community

Using the Community

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

Galleries

Galleries

Looking for inspiration on how to present your data? Need instructional videos? Check out our Galleries!

Top Solution Authors