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.
Jen I only have one level of accounts therefore I cannot refer to your example. I dont have an account hierarchy...
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.
Subtotal in Rows-AP.twbx 219.7 KB
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?
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).
Step 4 - Add Subtotals
- 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
Subtotal in Rows-AP.twbx 271.6 KB
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?