Forum Discussion

orana's avatar
orana
Icon for Helper I rankHelper I
5 years ago

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. 

Need Help

7 Replies

  • lbendlin's avatar
    lbendlin
    Icon for Super User rankSuper User
    5 years ago

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    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.

    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. 

    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.

  • orana's avatar
    orana
    Icon for Helper I rankHelper I
    5 years ago

    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. 

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      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]
    )

      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.

      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.

      • orana's avatar
        orana
        Icon for Helper I rankHelper I
        5 years ago

        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.