Transpose a card

user01295

Is it possible to transpose a domo card? I'm trying to create something like a profit and loss statement which has calculated fields (that needs to recompute on the fly, ETL doesn't help me do that as far as I know). I can use a table card to do this perfectly horizontally - with my metrics as columns and each line representing a business dept. However, a P&L should be vertical with metrics/calculated fields appearing as rows.

 

So is there something like a transpose I can do? or any other solution to my problem?

BlueRooster

We've recently recreated a P&L in Domo. I'll try to give a rough idea of how we did it.

 

We're using a table card and have our columns set as follows:

Line Type | Line Item | Calculation1 | Calculation2| Etc..

 

We're using the Subtotal Rows and Hide Counts options so that Line Types are displayed once, with each corresponding line item for that type displayed in the next column along with their calcuations. Here's a sample picture of what it might look like.

So in Domo, the first two columns are dimension fields and the rest are the calculated measures.

 

Hope that helps. If you have any additional questions, let me know.

 

Sincerely,

ValiantSpur

 

**Please mark "Accept as Solution" if this post solves your problem
**Say "Thanks" by clicking the "heart" in the post that helped you.

user01295

Thanks so much for the reply. I do have some more questions though, since I'm rather new to DOMO. How is your dataset organized?

 

I tried two variants-

 

Business	Dept	Sales	Cost
Clothing	Kids	80	21
Clothing	Men	70	54
Clothing	Women	50	35
Food	Cooked	62	65
Food	Fresh	60	15
Food	Packed	54	35
Furniture	Indoor	63	32
Furniture	Outdoor	50	35

 

and

 

Business	Dept	Metrics	Values
Clothing	Kids	Cost	21
Clothing	Kids	Sales	80
Clothing	Men	Cost	54
Clothing	Men	Sales	70
Clothing	Women	Cost	35
Clothing	Women	Sales	50
Food	Cooked	Cost	65
Food	Cooked	Sales	62
Food	Fresh	Cost	15
Food	Fresh	Sales	60
Food	Packed	Cost	35
Food	Packed	Sales	54
Furniture	Indoor	Cost	32
Furniture	Indoor	Sales	63
Furniture	Outdoor	Cost	35
Furniture	Outdoor	Sales	50

 

Ideally this is what I want my card to look like, with filters for Business and Dept, and Profit metrics being calculated fields-

 

Sales	489
Cost	292
Profit	197
Profit%	67%

 

Your method of offsetting columns can also work. But I'm not sure how your dataset looks or if it even matters.

BlueRooster

If your data is formatted as described, you should be good to go. 

 

I imported what you gave me and here's what I came up with quickly:

The beast mode calculations I'm doing for Cost and Sales specifically are:

CASE WHEN `Metrics` = 'Cost' THEN `Values` END

(replace Cost with Sales for ther Sales Beastmode)

 

Obviously, you can create more complicated Beast Modes if needed but as long as your summarizing on those columns it will keep them grouped as shown above.

 

I hope I explained that well (not sure if I did)

Let me know if you have any other questions, and I'll try again lol.

 

-ValiantSpur

 

**Please mark "Accept as Solution" if this post solves your problem
**Say "Thanks" by clicking the "heart" in the post that helped you.

user01295

I guess I should have made myself clearer. This is the final domo card I'm trying to create:

 

Business Filter | Dept Filter

Card:

Sales	489
Cost	292
Profit	197
Profit%	67%

 

The first two tables in my earlier reply are the variants of datasets I have.

 

Maybe I'm missing soemthing here, but I'm not able to make the connection from what you have shown to having metrics vertically with additional calculated fields. 

BlueRooster

So the problem is that the way you're wanting to display it (vertically with multiple metrics) there isn't a way to calculate all those metrics while including all the rows every time. The way to think about using BeastModes is that for every bucket your calculation is used in (Sales, Cost, Profit) the rows in the dataset on the backend can only go into once. So if I use my Sales #s for Sales I can't then use them for Profit later. 

 

To do what you're wanting you would need to break Sales and Cost into seperate columns and then break your metrics out horizontally so that each Metric can call Sales and Cost. Using the select I gave you here:

https://dojo.domo.com/t5/Beast-Mode-ETL-Dataflow/Do-a-quot-Sumif-quot-using-CASE-that-s-independent-of-other/m-p/30306#M4324 

 

You can take the results, calculate multiple BeastMode metrics horizontally and still have your optional Filters for Line of Business and Dept.

 

If you absolutely had to have them vertically, the only way I can think of to achieve something like that would be to summarize the numbers in a dataset and then use that, but you would lose the functionality of filtering it on Business and Dept.

user01295

Thanks for the explanation! Huge help in understanding how cards work - helped me to reach my solution  (atleast the first part of it sigh  )

BlueRooster

Glad that was a help to you. 

 

If you get hung up on anything else, just let us know.

 

-ValiantSpur

 

**Please mark "Accept as Solution" if this post solves your problem
**Say "Thanks" by clicking the "heart" in the post that helped you.