Transpose sub-total into separate report column
I would like to create a grid column which is a transposed sub-total from a higher grain dimension attribute. I'd could do this in the underlying dimension but I wanted to see if it was possible in BI Office first.
Essentially, I have a report which looks like the bottom table (below) but missing the 'Grouped Change' column. I want to create this column based the total pct change for the entire group (i.e. relating to the 'Master Reference'). I don't however actually want to include the 'Master Reference' column in the report with subtotals for each master ref.
Is this possible in BI Office, through something like a custom calculated member? I'm struggling to find mdx which does this, otherwise I would do this as a calculated member in the Cube.
Hope this makes sense..
I think the Advanced Calculation Designer will help address your issue. I used hard coded years (Year 1 = 2014, Year 2 = 2015) and the master reference (Master reference and reference are both in my user hierarchy under Sales Team) so I could use the Ancestor option in the MDX. Here are my steps:
Step 1 - Create your Year 1 to Year 2 Growth Metric
Use the wizard to hard code to your years OR create a Current and Last Year metric in the cube or using BI Office. I hard coded in my example and called it Grwth% Year over Year.
Step 2 - Click on the Calculation Designer button under the Analysis Tab
The Calculation designer allows for all sorts of calculations without having to code MDX. I simply created a calculation that was (Current Year for All - Last Year All)/Current Year All. You can begin adding Data Points. If you haven't used the designer before, please review the help.
Step 3 - Select your time period and parent member
When you click on a Data Point you will see all the hierarchies. Click the ellipse button to current year (2015) and the Ancestor for the Sales Team.
Repeat this for the other two data points, with the second Data Point being 2014 and Ancestor and the third Data Point 2015 and Ancestor. Make sure to place the hierarchy with your other exact hierarchy. I saved it as Overall Grwth%.
Step 4 - Run your selection with both the parent and children.
See how it displays when you select the years, both metrics, and the region and sales reps.
Now when I deselect the parents, it should be close to what you would like.
You'll probably need to play with it a bit to get it right. The good news is you can capture the MDX behind the scenes. Just right-click on the Overall Grwth% member, select Edit Custom member,...
...and you will see the MDX.
You then might be able to use this to bring the calc into the cube, but there is so much you can do with the logic wizards in BI Office you might decide to bring in more logic here, especially with parameterization, variables, and other advanced capabilities in BI Office.
Best regards - John HormaecheaReply
Thanks for your quick reply. I went through your steps and it worked a treat!
Setting the Ancestor-Parent relationship between the Master Ref>Ref was the key. I was trying to script this out in the MDX but to no avail. It's great that the Calc designer has done this for me. This has definitely saved me a lot of time.
Cheers for the help