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

RayRay_H
RayRay_H Member
in Magic ETL

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

image.png

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.

Tagged:

Best Answers

  • marcel_luthi
    marcel_luthi Coach
    Answer ✓

    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
    jguzman Member
    Answer ✓

    @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.

    P&L.png

Answers

  • zaclingen_zips
    zaclingen_zips Member

    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
    RayRay_H Member
    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.

    image.png

  • marcel_luthi
    marcel_luthi Coach
    Answer ✓

    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
    jguzman Member

    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.

    P&L.png

  • RayRay_H
    RayRay_H Member
    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
    RayRay_H Member

    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
    jguzman Member
    Answer ✓

    @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.

    P&L.png

Categories