Creating a Second Y-Axis on an Analytic Chart


Did you know you can get a 2nd y-axis on a chart? This can create what some people call a mixed-series chart, with a combination of bars and lines.

If you add 2 measures to a Bar Chart, one of which has “normal” numeric formatting, and one which is formatted as a percentage, PerformancePoint will draw the % series on the right axis.

For example, here’s a simple chart with one bar series.

clip_image002

Add a 2nd measure to the query, and if it’s a percentage, PerformancePoint will automatically add it to the right axis. That’s it.

clip_image004

Note: Be sure to add your percentage measure as the first measure in the Series, that way the lines will draw on top of the bars. If the percentage measure is second, the lines will draw behind and may be hard to see.

We don’t have a way to manually control this yet, we are hoping to add that in a future version, but for now, you can create mixed bar/line charts as long as you’re using percentages.

Greg Bernhardt

Senior Program Manager – PerformancePoint

Microsoft

gregbern@microsoft.com


Comments (21)

  1. Did you know you can get a 2nd y-axis on a chart? This can create what some people call a mixed-series

  2. ybenya says:

    Thank you very much. This helps a lot. I just got back from PPS week training and instructor showed the screen shot, but had no idea how to do that…

    Thanks,

    Yuriy.

  3. LanceHsiang says:

    My configuration under evlaluation consists of the Proclarity desktop professional 6.3 + PPS + SQL Ent 2005. However, I can not find the screen like what Greg had posted.

    What I am going to do is to create a Two Y-axes chart:

    1. Y1: Stacked bar charts (say, in Product category)Sales amount in dollars (left axis)

    2. Y2: (Line chart) Overall Margin (mixed porducts) percentage (Right axis)

    3. Bottom axis : Time (year-quarter)

    I’d appreciate if somebody can help me with it.

    Thanks,

    Lance.

  4. MSDN Archive says:

    You have to start with a regular bar chart not stacked and then add a percent measure and a non-percent measure as outlined above. Or, you can build the chart in ProClarity and right click on the series you want to move.

  5. LanceHsiang says:

    Thanks for help.

    Please advise how can I attach a screen chop here to help communicate?

    I just used the "Getting started tutorial" in Proclarity as my example. My scenario is as follows:

    1. Row: sales amount

    2. Column : Product category

    3. I want to add "% Gross Margin" (from Measurement) in to the "Row" as my 2nd data series….to be plotted in Y2 axis.

    But when I applied, the Row was replaced by my 2nd selected measurement (% margin), that means, I can never add another data series in row, and further specify it to be plotted using Y2 axis.

    Please advise where did I go wrong with my Proclarity 6.3 DT professional? Or, was it due to version difference? since I could not see the screen like Greg posted…

    Appreciated.

    Lance.

  6. MSDN Archive says:

    You need to control click to add % gross margin in addition to sales amount. Once you have both on the rows you can right click on % gross margin and move the series to the y-axis (in ProClarity at least). If you are still having trouble email me at alysonp@microsoft.com

  7. pwhygle says:

    Is it possible to do this with two dimensions instead of 2 measures?

    I am playing with my MDX query to try and get it to render the graph in the manner you have above but I cannot figure it out.  In my case, my values are dimensions (one is a % and one is a $).  Any ideas?

    Thank you!

  8. MSDN Archive says:

    Only works for measures. Sorry.

  9. Lance Hsiang says:

    In case of the perspective view, does it support to differentiate the dimensions by colors of the data point?

    And further to display the 3rd measure by using the area size of the data point? (like bubble chart)

    Thanks for advice.

  10. yzhao says:

    how to display all of them in Legend, when there is more than 30 series?

  11. We had a need on how to add a second axis on an Analytic Chart and I found the following link. Very helpful…

  12. dsu says:

    i have requirement to have both in a single chart can we do like that in pps.

    Also how i can control the axis values if second axis we don’t want to display.

    Thanks in advance

  13. kanwarpalmanu says:

    Hi,

    We want the bar and the line in a similar manner, however, the line needs to be drawn for the last year actuals of a measure and the bars need to be drawn for Actual, Budget and Last year forecast.

    Is it possible to have a numeric measure for both Line and bar chart?

    Could someone please suggest a way to do this?

    Thanks in advance,

    Manu

  14. MSDN Archive says:

    Currently it is only possible to have the combined bar/line chart if one measure is a percentage.

    Sorry,

    Alyson

  15. pauldotnet says:

    This post helped me out a lot.  Thanks for posting.

    Is it possible to display the secondary y-axis and line(s) once a user drills down on the report?

    My organization is using the secondary y-axis to show satisfactory and excellent baselines.  When a user drills down on the chart, the baselines are no longer displayed on the report.  Management has requested that the baselines remain on the report no matter how the user drills down.

    Regards,

    Paul

  16. Jonas Lundin says:

    What about SharePoint 2010 with PPS. Is it possible to control th second Y-Axis manually. I would like one axis with sales quanitity and an additional with sales in local currency.

    Regards,

    Jonas

  17. Bhavesh Jain says:

    Is it possible that all the series are non Percentages?

  18. Chris says:

    A secondary axis is extremely important in the business world, and this solution does not provide a way to compare two measures (Qty On-hand, Units Sold) which is extremely important.  Or even better, average retail vs units sold.  If someone knows of a solution for this using an analytic chart, please post.

  19. PC says:

    This there any way of making the right Y axis a numeric value other than percentage?

  20. BJ says:

    Is there any way of making Y axis as numeric value other than percentage?

  21. Hasham Niaz says:

    Is there a way to draw multiple line chart on same measure based on different filters, called overlapping line charts like in Excel