3 Replies Latest reply on Jun 29, 2018 2:46 PM by teresa.chan.0

    Bars and Lines on the same chart

    teresa.chan.0

      Hi--I wanted to graph assets and liabilities together on the same panel, but have my assets shown as a bar chart, but my liabilities as a line.  Visually, this helps to show the breakdown of the assets, but provide that my liabilities are in line. My data is set up so that all my values are in a skinny column going down, and then my categories are assets and liabilities, along with the subcategories for assets. All my dimensions are set out as rows across.  I can't graph get my asset bars to filter out the liabilities however, while also getting liabilities to show up as a line. I tried using an if statement to change my liabilities to a measure value and add it on the graph, but the filter option on the assets filters out the liability portion, so the if statement doesn't work. I've posted an example of the bars on top, and the line on the bottom. My ultimate goal is to have both on one panel, but have the bar chart only show asset1 and asset2, and no liabilities.  Appreciate any insight/workaround. Thanks!                                                                                                        Here is an example of how I want it to look. I created this using all measure values but needed to change the format so that I can easily do pie charts, and also because I need to have two lines on the right axis eventually.

        • 1. Re: Bars and Lines on the same chart
          Wesley Magee

          Teresa,

          I'm not positive what you're looking for, but you're headed in the right direction. I would use IF/THEN statements to create a metric for assets and for liabilities. They would look like this:

           

          IF [Metric Name] = 'Assets' THEN [Metric Value] END

           

          You would then use a dual axis for the two two new metrics.

           

          The whole point is to make it so you don't have to use a filter. Ultimately you need all of the data in the view, so instead of using a filter you will use calculations to specify what data you want to see and where you want to see it.

           

          Let me know if this doesn't work for you.

          -Wesley

           

          If this post assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

          • 2. Re: Bars and Lines on the same chart
            teresa.chan.0

            Thanks Wesley,

             

            Appreciate your help. Ah I see, so it seems like filter isn't the right argument to use. But I'm not sure how to have the bars properly color each of the dimensions I want (assets) but have it remove liabilities at the same time.

             

            Let me try to summarize again in hopes I'm more clear this time around. I changed the example slightly to illustrate my final goal.
            I have 5 data points I want to graph: 3 asset values from 3 regions, and then two liability values from the northern and southern hemisphere to represent global liability exposure.  I don't have the specific breakdown of the liability values for those three regions (NorthWest, NorthEast, and PR), only the northern and southern hemisphere. I want to show that even though I only have 3 regions of asset values available (bar chart),  the global/total liabilities number is still lower than these assets combined (represented as two lines with 1 line being the northern hemisphere and 1 line being the southern hemisphere).

             

            I tried

             

            IF [Region] = 'NorthWest' THEN [Value] END. I repeated this for each of the 3 assets, and added all of them as measures to a bar chart. Then I'm stuck at how to add the 2 liability values as a measure value on the right axis (as shown below).  If I can somehow use [Region] as a measure and drag Measure Names to the color tab to break out each of the assets while removing the dimension liabilities, then I think I can get this to work.. Here is my updated work.  Thanks again!

            • 3. Re: Bars and Lines on the same chart
              teresa.chan.0

              Thanks again Wesley, I took your recommendations and added in the level of detail syntax to arrive at the data I need. Very helpful to learn that I shouldn't filter out the data, and instead use the level of detail and if statements to only pull the dimensions that I want to display. This effectively removes any null dimensions, and allows me to chart bars on one side, and multiple lines on the other.
              Thanks!
              Teresa