Custom P&L - Design problems

nlonkai

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

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

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

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

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!

 

 

ST_Superman

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.

 

Unknown

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

 

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.

 

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.

 

 

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!

rado98

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.

 

 

Unknown

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

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.

 

Unknown

I was able to create individual subtotals.

 

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

 

 

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

 

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

BlueRooster

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

ST_Superman

Nice.  You beat me to linking my own post ?

rado98

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

rado98

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.

 

Category	Sub Category	Type	Account	$		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

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

 

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.

 

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.

 

 

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!