How to add gross profit row into profit and loss pivot table

RayRay_H

I am using pivot table to create a profit and loss statement. Please see the following picture:

I want to add a row which is called Gross profit below TOTAL COST OF SALES. Gross profit is calculated by subtracting TOTAL COST OF SALES from TOTAL TRADING INCOME. Also, I don't want to create a Gross Profit account and join it with the raw account dataset. May I get some help on how to do calculations on subtotals like this? Thanks in advance.

marcel_luthi

For each row in a Pivot table, a value needs to exist, so without joining a new Gross Profit Account, having this to display would be hard to say the least, and even then you'd need to work with the fact that the measure calculation will need to be worked differently on that one than on the others.

You might be able to achieve a similar outcome, using a Megatable and transposing it (rows being the months), that way the columns (Beast Modes with case statements on the Account) to represent the Trading Income, Cost of Sales, etc will become rows, but if you want the breakdown (Sales, Purchases, Interest Income, etc) to also be displayed without drilling down you'd need to create a Beast Mode for each and whenever a new one shows or one does not have entries, they won't be automatically added/removed.

Would love to see if anyone has other ideas that could come in handy for this.

jguzman

@RayRay_H yes that's definitely a limitation and I think that's partially why they created the domo finance app (you can search for it in the appstore). Also, wanted to update you that we did follow the suggestions here and created the P&L calcs through dataflows and weren't too bad at all to do. But now it makes for a much cleaner looking P&L… basically there are no aggregations or calcs anymore at the card level… we just display the metrics and calcs together.

zaclingen_zips

Have you tried using a Beastmode to complete this? I feel like you could create a calculated field for Gross Profit pretty easily.

Unless I am misunderstanding your goal.

RayRay_H

https://community-forums.domo.com/main/discussion/comment/69115#Comment_69115

Thank you for your comment.

Yeah, beast mode can calculate gross profit easily but I don't know how to insert this Gross Profit just below the Total Cost of Sales without joining a second table. Just like the following excel table shows, another row of Gross Profit should be added into pivot table. If I add the Gross Profit calculated by Domo beast mode to the pivot row, each row will have Gross Profit subtotal, which is not what I want to achieve.

marcel_luthi

For each row in a Pivot table, a value needs to exist, so without joining a new Gross Profit Account, having this to display would be hard to say the least, and even then you'd need to work with the fact that the measure calculation will need to be worked differently on that one than on the others.

You might be able to achieve a similar outcome, using a Megatable and transposing it (rows being the months), that way the columns (Beast Modes with case statements on the Account) to represent the Trading Income, Cost of Sales, etc will become rows, but if you want the breakdown (Sales, Purchases, Interest Income, etc) to also be displayed without drilling down you'd need to create a Beast Mode for each and whenever a new one shows or one does not have entries, they won't be automatically added/removed.

Would love to see if anyone has other ideas that could come in handy for this.

jguzman

We created something similar for our P&L where we needed to show net income or ebitda… we did this by creating a beastmode field to calc the two based on subtracting or adding revenues vs expenses and created a filter to toggle the grand total between the two.

RayRay_H

https://community-forums.domo.com/main/discussion/comment/69142#Comment_69142

Thank you very much for your explanation.

I tried to use the Megatable transpose function but seems the total and subtotals are on the columns too, not on the rows.

RayRay_H

Thank you for sharing your way of achieving this. This is a good idea😄 but it seems this is one of the limitations of domo that domo can't put gross profit with all other totals/subtotals together on a same table?

jguzman

@RayRay_H yes that's definitely a limitation and I think that's partially why they created the domo finance app (you can search for it in the appstore). Also, wanted to update you that we did follow the suggestions here and created the P&L calcs through dataflows and weren't too bad at all to do. But now it makes for a much cleaner looking P&L… basically there are no aggregations or calcs anymore at the card level… we just display the metrics and calcs together.