June 25, 2009 at 9:24 am
Is there a way to show the normal value and the % of total next to it in a pivot table from data connected to a SQL Server 2005 Analysis cube? I know this isn't an excel forum, but, I thought I would give it a shot since I have had some good luck here lately. The only way I can think of to do this is to create a calculated field in the data cube with using the same measure and use that field to show % of total. It just seems like there should be an easier way.
🙂
Thanks!
Marc
June 25, 2009 at 10:51 am
Marc
Add your value field to the data area a second time. Excel will designate the results as Sum of Value2. Right click on the row/column header for this data. Select Field Settings. Change the name of the field (% of Value). Click the Options button at the lower right. In the “Show data as:” dropdown box select % of column. Click OK. This will give you the percentage for the column as well as the aggregation for Total % of Value.
If you have another version of Excel, you might not find things in the same place.
Ron
June 25, 2009 at 11:08 am
Thanks. I tried that. It doesn't allow you to do that in Excel 2007 with a data cube for some reason, unless I'm missing something somehwere. I appreciate the feedback.
June 25, 2009 at 11:53 am
Marc,
Not sure what control you have on your environment, but if you can, download and install the PivotTableExtensions (it works in Excel 07). They can be found here.
Once you've done that, you put ABC on rows, MeasureX in the values. Then right click on a row, go to PivotTableExtensions menu option. Add a new calculated member, let's call it Mx, set it to be [Measures].[MeasureX]. Then change the value settings on either MeasureX or Mx to display as % of total.
it's not overly pretty but does work and is quick to implement. Also transfers with the workbook - ie it embeds the calc member into the MDX script (or so i'm lead to believe 😉 ).
HTH,
Steve.
June 25, 2009 at 12:29 pm
That was exactly what I was looking for. Thank you!
Marc
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply