I am trying to create multi year P&L views, with months in the columns. After the last month of the year, I want to show the annual total, YoY $ value, and YoY % change. (Months->Annual Total->YoY $ change->YoY % change).
The challenges I’m encountering are as follows - example with numbered notes shown in snippet below:
- Inability to choose position of aggregators. Pros of using aggregators vs calculated items are that gross margin % calcs roll up correctly, and the values are retained when I collapse the months and only want to show the annual total.
- Gross margin % calculated items roll up incorrectly. It displays the sum of the months’ percentages, rather than the annual gross profit value divided by the annual revenue value. Pro of calculated item is ability to choose column positioning.
- Is there any way to hide a value in a calculated item? The YoY % calculation displays a nonsensical value when calculating against % margin values. Only workaround I’ve currently identified is formatting the individual cell with white text and background to effectively be blank, but this doesn’t scale well as the formatting doesn’t apply year over year and the value still exists in excel exports..
Are there any workarounds or approaches that I’m not considering here?
You can edit the “formula” of a calculated at the cell level. For example to hide the YoY%, right click on the cell > Edit this calculation > No calculation should remove it
You should also be able to do a specific calc for your Gross Margin % doing this
I’m also not seeing any calculation options for the gross margin % that would work. How would you approach this? This is one of the most typical column views (Months->Annual Total->YoY $ change->YoY % change) I’ve seen in reports so surely a solution to this has been identified before.
Sorry, “Edit calculation for this Metric” will do it.
If you select the application variables at the cell level, it will work yes
@Nathan. Do you have a recommendation for how to make the gross margin % calc work?
@Nathan , curious of you have any thoughts here. Let me know if I’m missing something.
@Justin Groover ,
As your Gross Margin % is not a separate metric but calculated with “Show values as” I don't have a solution right away but have to think about another way of doing it.
Maybe the best way is to submit this as a support ticket to get private help on this.