2 Replies Latest reply on May 4, 2017 3:47 PM by Shinichiro Murakami

    How to get a bar and a stacked bar in the same chart ?

    sahil batra

      Hi,

       

      I need to get a bar & stacked bar in the same bar chart. Attaching an example sample work book using Sample data store. I basically need Profit & Sales bar as Stacked in one Stacked bar &  the remaining two bars to be Discount & Profit Ratio.

       

      Appreciate help in this regard.

        • 1. Re: How to get a bar and a stacked bar in the same chart ?
          Jay Morehart

          Not sure if this is the best way but I think it gets what you are looking for;

          Go back to the data source and pivot [Sales] and [Profit]

          go to the sheet, delete the sales and profit fields that have red exclamation marks

          create calculated fields for sales and profit (if you can duplicate these in the data source instead, that is better)"

          [Sales] = { FIXED [Order ID]: Max(Case [Pivot Field Names] when "Sales" then [Pivot Field Values] else null end )}

          [Profit] = { FIXED [Order ID]: Max(Case [Pivot Field Names] when "Profit" then [Pivot Field Values] else null end )}

          this should fix any broken calculations or sheets using Sales and Profit

           

          Then just dual axis Measure values and SUM([Pivot Field Values])

          For measure values [Measure Names] on color

          for [Pivot Field Values] -- [Pivot Field Names] on color

           

          Resulting the following, which I think is what you were looking for (workbook attached as well)

           

           

          Hope this helps!

           

          NOTE: this will break any views using [Number of records] as the pivot doubles the number of records in the view, using countd(order ID) (in the case of superstore) should solve any problems regarded this.

           

          let me know if this works in your use case, I have never seen a requirement like this.

          2 of 2 people found this helpful
          • 2. Re: How to get a bar and a stacked bar in the same chart ?
            Shinichiro Murakami

            Hi Jay and Sahil

             

            Jay's solution is kind of straight forward and feasible. but here is a very strange/tricky approach.

            Just as your info because this cannot be common solutions.

             

            Looks like stacked bar chart, but actually it's gantt + reference line.

             

             

             

            Thanks,

            Shin