Custom P&L - Design problems

Hi there,

I need help from you guys.

I want to create a custom P&L. Unfortunately, I can´t figure out the design part.

My current version in excel has the following structure:

 

example structure

 

grafik.png 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

So far, I was able to add Ranks and Categories to my Dataset, so I can have a correct order of my accounts. Unforunately, I have a big issue with subtotals as you can see on the screenshot.

 

my current version in DOMO

Random Example DOMO PL.jpg

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Is it somehow possible to add custom totals, which I can move freely?

On my example screen, I would like to have a total under Ranking 3, which contains ranking 1 + ranking 2 + ranking 3.

 

Under ranking 6 for example should be a total of ranking 1 +2 +3 + 4 + 5 + 6

 

Furthermore, does someone know if I can hide the ranking column, it is just implemented for clustering accounts.

 

I hope I was clear enough with my problem, it is a bit difficult to describe.

 

Many thanks!

 

 

Best Answers

  • ST_-Superman-_
    Answer ✓

    I think you will want to restructure your data set a bit.  If you can get a "flat" file with your categories and subcategories defined and labeled.  You should be able to create the visualizations you are looking for, or use a Sumo card to create a pivot table with the information.1.png

     


    “There is a superhero in all of us, we just need the courage to put on the cape.” -Superman
  • Unknown
    Answer ✓

    I was able to create a semi working P&L.

     

    Thanks to everyone for helping me out. I used a lot of input from all of you!

     

    Maybe someone wants to create one as well, so here we go:

     

    1) Create a chart of accounts including all required infos.

    In my case: accounts, account name, category, sub category, ranking (necessary for calculations).

     

    2) create one DOMO Dataflow where you join your real accounting with your chart of accounts

    =accounting dataflow

     

    3) Create another DOMO Dataflow with "accounting dataflow" as your input and create all required calculations.

    In my case I used a case function on rankings.

    result 1: case ranking < 1 then sum...

    result 1: case ranking < 2 then sum...

    =calculation dataflow

    DOMO PL calculations.jpg

     

    4) Create  a ETL where you combine "accounting dataflow" and "calculation dataflow".

    Special thanks to @ST_-Superman-_ for his collapse/uncollapse function.

    You need to collapse the calculation dataflow, because you want to have all information in rows not columns.

    DOMO PL ETL.jpg

     

    5) Append "accounting dataflow" and "calculation dataflow".

     

    6) Make a drill down table. So your first Chart are all calculations and then you can click in each calculation to receive an overview of your groups. I did another drill down on account level.

    DOMO PL.jpg

     

    DOMO PL drill.jpg

     DOMO PL drill 2.jpg

    The semi part is that I have to delete filters when drilling down, which is not the most convenient solution, but I don´t know how to fix that.

     

    I hope my explanation is understandable.

     

    Thanks!

Answers

  • rado98
    rado98 Contributor

    Hi

    You can hide any columns in hte General chart properties.

    As for custom subtotals, I dont think so.

     

    If you need custom subtotal, what you can do is:

    Create duplicates, triplicates and or quadruplicates of all the rows but rename the amount and budget columns as Amount Duplicate, etc.

     

    That way suign beasmodes you can create

    Acoount 1 by adding up Amount

    Subtotal (1+2+3) by adding up Amount Duplicate

    Subtotal (1+2+3+4+5+6) by adding up Amount Triplicate

    Total by adding up Amount Quadruplicate

    and so on.

    It would be a royal pain so I would not do it myselft, I wuld try to dsiplays things in a different way.

     

     

  • Hi,

    many thanks for your suggestion.

     

    I am not totaly sure what you meant with making duplicates, triplicates. Wouldn´t be there more columns then?

     

    thx

     

  • ST_-Superman-_
    Answer ✓

    I think you will want to restructure your data set a bit.  If you can get a "flat" file with your categories and subcategories defined and labeled.  You should be able to create the visualizations you are looking for, or use a Sumo card to create a pivot table with the information.1.png

     


    “There is a superhero in all of us, we just need the courage to put on the cape.” -Superman
  • I was able to create individual subtotals.

     

    The only thing is that I can´t display them properly.

    Screenshot(2).png

     

     

    Does someone know, how I can change axes in a table? It should look like this:

    Clipboard01.jpg

     

    Basically, I think all the information are there now, but I can´t figure out the design part.

     

    Hope someone has an idea

     

    many thanks

  • You should be able to use the collapse columns function in an ETL. @ST_-Superman-_ put together a great walkthrough on this process here:

    https://dojo.domo.com/t5/Beast-Mode-ETL-Dataflow/Collapse-Uncollapse-Columns/td-p/25067

  • Nice.  You beat me to linking my own post ?


    “There is a superhero in all of us, we just need the courage to put on the cape.” -Superman
  • rado98
    rado98 Contributor

    I will depend on how the new columns were generated, if they come out fo beast modes collapsing unfortunately will not work 

  • rado98
    rado98 Contributor

    If collapsing will not work you cna trying the following (continuing form ST's data set restructure)

    In Magic ETL (sorry I am dont really do SQL) create a new set of rows with the total of each Category/Subtegory and append it to the original data set.

    After that in your table card create a beast mode that will collect of the label (this can be in ETL too I suppose.

    Create a best mode to order the labes from above.

    This should give you what you want I think  and should be fairly scalable and manageable.

     

    CategorySub CategoryTypeAccount$ Beastmode Label$
    Total Accounts 1-11     Total Accounts 1-11 
    Total Expenses     Total Expenses 
     Total Accounts 1-8    Total Accounts 1-8 
     Total Personnel Exp    Total Personnel Exp 
     Total Operating Expenses    Total Operating Expenses 
      Sales Cat 1   Sales Cat 1 
      Discounts   Discounts 
      Sales Cat 2   Sales Cat 2 
      Operating Expenses Cat 3  Operating Expenses Cat 3 
          Account 1 
          Account 2 
          Account 3 
          Account 4 
          Account 5 
          Account 6 
          Account 7 
          Account 8 
  • Unknown
    Answer ✓

    I was able to create a semi working P&L.

     

    Thanks to everyone for helping me out. I used a lot of input from all of you!

     

    Maybe someone wants to create one as well, so here we go:

     

    1) Create a chart of accounts including all required infos.

    In my case: accounts, account name, category, sub category, ranking (necessary for calculations).

     

    2) create one DOMO Dataflow where you join your real accounting with your chart of accounts

    =accounting dataflow

     

    3) Create another DOMO Dataflow with "accounting dataflow" as your input and create all required calculations.

    In my case I used a case function on rankings.

    result 1: case ranking < 1 then sum...

    result 1: case ranking < 2 then sum...

    =calculation dataflow

    DOMO PL calculations.jpg

     

    4) Create  a ETL where you combine "accounting dataflow" and "calculation dataflow".

    Special thanks to @ST_-Superman-_ for his collapse/uncollapse function.

    You need to collapse the calculation dataflow, because you want to have all information in rows not columns.

    DOMO PL ETL.jpg

     

    5) Append "accounting dataflow" and "calculation dataflow".

     

    6) Make a drill down table. So your first Chart are all calculations and then you can click in each calculation to receive an overview of your groups. I did another drill down on account level.

    DOMO PL.jpg

     

    DOMO PL drill.jpg

     DOMO PL drill 2.jpg

    The semi part is that I have to delete filters when drilling down, which is not the most convenient solution, but I don´t know how to fix that.

     

    I hope my explanation is understandable.

     

    Thanks!

This discussion has been closed.