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

Published 12 February 08 04:30 by alysonp

Comments

# Noticias externas said on February 12, 2008 11:33 AM:

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

# ybenya said on March 5, 2008 2:19 PM:

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.

# Lance Hsiang said on March 31, 2008 7:06 AM:

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.

# alysonp said on March 31, 2008 9:31 AM:

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.

# Lance Hsiang said on March 31, 2008 10:05 PM:

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.

# alysonp said on April 1, 2008 10:20 AM:

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

# pwhygle said on April 1, 2008 11:54 AM:

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!

# alysonp said on April 1, 2008 12:17 PM:

Only works for measures. Sorry.

# Lance Hsiang said on April 3, 2008 5:58 AM:

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.

# yzhao said on July 5, 2008 9:30 PM:

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

# Microsoft Business Intelligence Blog said on September 30, 2008 2:40 PM:

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

# dsu said on November 9, 2008 7:32 PM:

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

# kanwarpalmanu said on January 9, 2009 7:17 AM:

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

# alysonp said on January 9, 2009 11:27 AM:

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

Sorry,

Alyson

# pauldotnet said on March 25, 2009 1:36 PM:

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

Anonymous comments are disabled

Search

This Blog

Microsoft BI Sites

Syndication

Page view tracker