cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
orana
Helper I
Helper I

Link the sort order between two bar chart

Hello,


I have two bar charts which shows different information from the same table. Bar chart 1 shows the customers who have purchased product X, while bar chart 2 shows historical units purchasing pattern of product B. Currently each bar chart is sorted by desceding order (e.g. the customer who's purchased the most is on top). I would like the second bar chart to sorted such that it linked to the order of the first bar chart. 

7 REPLIES 7
orana
Helper I
Helper I

Hey, sorry for not making this clear. The image below is how my data is structured. I would like to have the 2nd chart on the bottom sorted by the first chart on the top. In this example, the order of the second chart would be Abe, Ashley, and Harry. Jackie would be last or if we can make it such that Jackie does not show up on the 2nd  chart that would be helpful. example.PNG

RicoZhou
Community Support
Community Support

Hi @orana 

I update the Rank calculated column.

Rank =
VAR _T =
    ADDCOLUMNS (
        SUMMARIZE ( 'Table', 'Table'[Customer], 'Table'[Manufacturer] ),
        "Rank",
            RANKX (
                FILTER ( 'Table', 'Table'[Manufacturer] = EARLIER ( 'Table'[Manufacturer] ) ),
                CALCULATE (
                    SUM ( 'Table'[This Year Units Sold] ),
                    FILTER (
                        'Table',
                        'Table'[Manufacturer] = EARLIER ( 'Table'[Manufacturer] )
                            && 'Table'[Customer] = EARLIER ( 'Table'[Customer] )
                    )
                ),
                ,
                ASC,
                DENSE
            )
    )
RETURN
    SUMX (
        FILTER (
            _T,
            'Table'[Manufacturer] = "a"
                && 'Table'[Customer] = EARLIER ( 'Table'[Customer] )
        ),
        [Rank]
    )

1.png

Add Rank column into Tooltips in second visual and set Rank column in filter field to show items is not blank. Then sort visual by Rank.

Visual is as below.

1.png

Best Regards,
Rico Zhou

 

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

Is there a way to incorporate product selection into the script? Such that if the top graph only showed mfg a and product 1 the bottom graph would follow the same order as the top graph. 

RicoZhou
Community Support
Community Support

Hi @orana 

I think you can build a Slicer by Product column. 

For reference: Slicers in Power BI

If this reply still couldn't help you solve your problem, please share a sample with me and show me a screenshot that the result you want.

 

Best Regards,
Rico Zhou

 

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

Excel file used in Power BI Power BI File 

 

Hi RicoZhou, the slicer doesn't seem to be working. To help, I created a power bi example and included the excel file as well.  Sheet 1 shows the two chart. The bottom chart should have "my" first. 

RicoZhou
Community Support
Community Support

Hi @orana 

Due to I don't know your data model, I build a sample to have a test. I think you want to sort bar chart2 by same sort in bar chart1. I think you can build a rank for Porduct X and then get the same rank for Product B. Here you need to have same key works like Customer or other values.

1.png

Rank:

Rank = 
VAR _T = ADDCOLUMNS(SUMMARIZE('Table','Table'[Procuct],'Table'[Customer]),"Rank",RANKX(FILTER('Table','Table'[Procuct]=EARLIER('Table'[Procuct])),CALCULATE(SUM('Table'[Value]),FILTER('Table','Table'[Procuct]=EARLIER('Table'[Procuct])&&'Table'[Customer]=EARLIER('Table'[Customer]))),,ASC,Dense))
return
SUMX(FILTER(_T,'Table'[Procuct] = "X"&&'Table'[Customer] = EARLIER('Table'[Customer])),[Rank])

Result is as below. First bar chart is sort by descing. And Here we sort the column in X axis in bar chart 2 by Rank column then sort bar chart 2 by X axis in bar chart2. 

1.png

Best Regards,
Rico Zhou

 

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

lbendlin
Super User
Super User

That ask is not clear to me. Can you provide an example?

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Power BI October Update 2021.jpg

Power BI Release

Click here to read more about the October 2021 Release!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!