8 Replies Latest reply on Aug 9, 2018 2:49 PM by yannis vossos

# Calculating Subtotal in Rows & Variance in Columns - is it Possible?

Hey Guys, I need to create a report as below. I have an oracle star schema, my fact measure is "Total" and I also have dimensions , Account and Scenario.

I need subtotals of accounts in the lines, and variance of Budget - Actual (scenario dim) in columns between scenario Actual & Budget;also need a kpi arrow or color based on the value of the variance

I tried many ways to no avail, I created both my account and scenario dimensions as measures each time. But not both , it seems impossible is it even possible?

• ###### 1. Re: Calculating Subtotal in Rows & Variance in Columns - is it Possible?

Have you tried using the built-in feature for Totals & Subtotals under Analysis menu? Refer to below links for solution:

If you find this helpful, please mark the post accordingly. Thank you.

• ###### 2. Re: Calculating Subtotal in Rows & Variance in Columns - is it Possible?

Sample workbook attached with the solution.

1 of 1 people found this helpful
• ###### 3. Re: Calculating Subtotal in Rows & Variance in Columns - is it Possible?

Jen I only have one level of accounts therefore I cannot refer to your example. I dont have an account hierarchy...

• ###### 4. Re: Calculating Subtotal in Rows & Variance in Columns - is it Possible?

In that case you will need to create a break-down on the single level? Sorry hard to understand but you can create a Group and hide the group by right clicking on the Pill and uncheck "Show Header" -- see attached workbook.

• ###### 5. Re: Calculating Subtotal in Rows & Variance in Columns - is it Possible?

Yes  a break down on single level correct!

How can I do that, also need some help to understand the pills you created (copies) can you please elaborate the steps?

Many Thanks

yannis

• ###### 6. Re: Calculating Subtotal in Rows & Variance in Columns - is it Possible?

yannis vossos below are the steps. I have also added them in the workbook that is attached. Hope this helps. Feel free to reach out if any further questions.

Step 1 - Create Desired Group

You can do so by right clicking any Dimension pill on the left and under Create select Group - For this view I have done so using Sub-Category field.

Step 2 - Create Dummy Pills

In this case I have created a Calcucation and using 0 (zero) as formula. You can check the 3 *-Pill(s) i have created on the Measures section - this will be required to achieve Column level formatting.

Step 3 - Build the View

This view is lot's of drag and drop.
- Start by Dimensions - Sub-Category (group) & Sub-Category drop these 2 into Rows self.  *Right click on Sub-Category (group) pill and Un-Check "Show Header" (this will hide the groups column in the view).
- Drag Date and select desired Year on Column self.
- Drag *-Pill onto the Columns Self
- On the Marks Card, set the Mark Type to Text
- Drag the desired Measure or Dimension onto the Text Shelf of the Marks Card.
- Format Lines and set from Zero Lines to None
- Right-click on the *-Pill axis at the bottom to Edit the axis. Clear all settings and turn off tick marks.
- Drag the same *-Pill onto the Header Self to set desired Title by editing axis, and turn off tick marks – change the Mark type to Polygon.

Repeat above steps for additional *-Pill(s).

- To add Subtotals - go to Analysis Menu and from drop-down select Totals and click on "Add All Subtotals"
- Right click on the Sub-Category and un-check "Subtotals"  - We only want to show totals at the Sub-Category (Group) level
- Rest is just formatting the view to adjust the way you want to have it as end-result

• ###### 7. Re: Calculating Subtotal in Rows & Variance in Columns - is it Possible?

Jen how can I do that Format Lines and set from Zero Lines to None?

Also how do I create the blank pill? And finally where can I rename the subtotals to meaningful account name?

Regards

yannis