Can you have a card with calculated rows that have different data types under one column?

I have a financial report that shows:

Row: 1 Gross Retail Revenue ($ value)

Row 2: Retail Discounts ($ Value)

Row 3: % Retail Discount = Retail Discounts/ Gross Retail Revenue (% Value)

Row: 4 Gross Wholesale Revenue ($ value)

Row 5: Wholesale Discounts ($ Value)

Row 6: % Wholesale Discount = Wholesale Discounts/ Gross Wholesale Revenue (% Value)

While I was able to create a SQL transformation to get the calculations, the card requires a column to have only one data type ($ or %). I need this report to be formatted with these rows having different data types. Any help is greatly appreciated.

Thanks!

Answers

  • @user074136

    some cards under "general settings" have an option to "transpose" the data.

    but in general

    1) don't preaggregate your data using SQL, this will limit your ability to filter your data. instead try to use beast modes in analyzer as a surrogate for SQL aggregation

    2) in one column, percent and currency are both numeric data types, so what you want to do is change formatting, which is a display question (use a CASE statement and build in the logic for a formatting string.)

    something like this, https://youtu.be/YgevJkjeFqw?t=644 will get you started.


    good luck!

    Jae Wilson
    Check out my 🎥 Domo Training YouTube Channel 👨‍💻

    **Say "Thanks" by clicking the ❤️ in the post that helped you.
    **Please mark the post that solves your problem by clicking on "Accept as Solution"
  • Hi @user074136

    Domo doesn't allow for different data types in the same column. To get around this you can utilize a beast mode to customize the formatting of each row manually with several CASE statements:

    CASE WHEN `row name` = 'Gross Retail Revenue` THEN CONCAT('$', ROUND(`value`, 2))
    WHEN `row name` =  '% Retail Discount', THEN
    CONCAT(ROUND(`value`, 2), '%')
    ...
    END
    


    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • @GrantSmith I have tried this approach, but this turns the data column into a text column and because I'm using a pivot table, the values section in this type of table has to be a numeric data type.

  • You'd need to pivot your data yourself then outside of a pivot table card (using a dataflow or other mechanism) and utilize a standard table card with your pivoted data

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • @GrantSmith Thank you!