Transpose a card

user01295
user01295 Member
in Archive

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?

Comments

  • BlueRooster
    BlueRooster Domo Employee

    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.

    image.png

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

    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-

     

    BusinessDeptSalesCost
    ClothingKids8021
    ClothingMen7054
    ClothingWomen5035
    FoodCooked6265
    FoodFresh6015
    FoodPacked5435
    FurnitureIndoor6332
    FurnitureOutdoor5035

     

    and

     

    BusinessDeptMetricsValues
    ClothingKidsCost21
    ClothingKidsSales80
    ClothingMenCost54
    ClothingMenSales70
    ClothingWomenCost35
    ClothingWomenSales50
    FoodCookedCost65
    FoodCookedSales62
    FoodFreshCost15
    FoodFreshSales60
    FoodPackedCost35
    FoodPackedSales54
    FurnitureIndoorCost32
    FurnitureIndoorSales63
    FurnitureOutdoorCost35
    FurnitureOutdoorSales50

     

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

     

    Sales489
    Cost292
    Profit197
    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
    BlueRooster Domo Employee

    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:

    image.pngThe 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 Smiley Frustrated (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
    user01295 Member

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

     

    Business Filter | Dept Filter

    Card:

    Sales489
    Cost292
    Profit197
    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. Smiley Sad

  • BlueRooster
    BlueRooster Domo Employee

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

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

  • BlueRooster
    BlueRooster Domo Employee

    Glad that was a help to you. Smiley Very Happy

     

    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.

This discussion has been closed.

Categories