This topic contains 9 replies, has 4 voices, and was last updated by 
-
Posts
-
I’ve looked around for an answer, but it’s hard to express my question in search terms. Is it possible to display an aggregate measure in addition to the values columns? Like this, say:
<pre class=”lang:default decode:true ” title=”Example”> | 2013 | 2014 | 2015 | Total | Max
US | 12 | 10 | 20 | 42 | 20
CA | 8 | 13 | 11 | 32 | 13
MX | 10 | 7 | 12 | 29 | 12
So, in this case, ‘Max’ would be the extra aggregate measure I want to add, while the years and Total come from the ‘Year’ column values.Hopefully the code block is showing up better for you all than it is for me.
Hello volfied,
Yes. I can think of at least three ways using Power Pivot. Please see attached workbook for 1 way that uses a pivot table set.
Probably the most useful way would be using “cube” formulas, but I would need to know whether you are working in Excel 2010 or Excel 2013 (slight difference between two versions) before creating.
Thanks, Tom! Excel 2013
Please see the sheet titled “Demo” in the attached work book, it looks like ordinary data entered by a users, but it was created from a pivot table and you will see references to functions “CUBEVALUE” and “CUBEMEMBER”, but do not think it is something complicated to do.
If you would like to try out creating a cube formula report, read on.
Starting with the sheet titled “Pivot to Start With”, the following instructions were written to take you step-by-step to what is shown on the “Intermediate Results” tab. We are covering a lot of ground within the following few steps, so I am saving the re-naming of certain cells until the next post.
Select a cell inside the Power Pivot pivot table on the “Pivot to Start With” tab.
(Note: I have already turned off Excel’s subtotals and grandtotals for this pivot)
In the PivotTable Tools context menu, select Analyze, then in the Calculations group, select the drop down for OLAP Tools
Choose the “Convert to Formulas” option (and the “hard” part is done)
Select the entire columns of D, E, G and H (by clicking in their headers while holding down the Ctrl key), then right click and choose the Delete option (all four columns should disappear)
Remove contents of cells (only) where the cell contents are “Column Labels”, “Row Labels”, “2013”, “2014”, and “2015”
Your results should now look like those on the “Intermediate Results” sheet.The re-naming effort, also not difficult, I will describe in the next post when you are ready to go forward (and that should complete the basics of using cube formulas as far as the current topic is concerned).
If you have any questions while working on the attached workbook, let me know, and I will do my best to answer them.
Ahh, yes. This approach hadn’t occurred to me. It should work nicely if I’m not expecting to do many gymnastics with the Pivot Table.
I’m intrigued by this Sets business from your first reply. I think I’ll look into it and see if it’s closer to what I’d envisioned.
Thank you for your help, Tom!
Your welcome; glad to be helpful.
In their book, “Power Pivot Alchemy”, Rob Collie and Bill Jelen show how to work with “Named Sets and ‘Asymmetric’ Pivots” on pages 19-22 (in the book, their example works with the option “Create Set Based on Row Items”, but in my example attached to a prior post, I choose “Create Set Based on Column Items”).
Looking forward to working with you again in the powerpivotpro forums!
—-
Note: It sounds like you are familiar with creating cube reports, and perhaps also with creating custom display names in formulas that use the CUBEMEMBER function. I was considering attaching a second workbook showing how to create such custom names, but since you may already know how-to, I will hold off. If someone else reading this post is interested in this naming process, I will be glad to attach an example workbook.
Dear Tom please help
I have P&L report throw 2 years. I want in 3rd column to show percenatege of changes between those2 years. I have no idea what to do.
Thanks in advance
To damiruka,
You should start a new topic by entering in your information in the areas at the bottom of the following linked page:
You may also be able to get at what your looking for by using the topn function in a measure. Sorry, on the road, so can’t open the excel docs to give you the exact formula, but in my head it should work. If not, just disregard! 🙂
The forum ‘Power Pivot’ is closed to new topics and replies.