How do I calculate a row total?

My columns also have text and numbers. I want to sum the numbers, ignore the "N/As"


Tagged:

Answers

  • I would suggest creating a Magic ETL and clean your data so that you have a numeric column and then you can use the built in chart properties to total different columns. You could also create a beast mode field to do the same thing. Replace your N/As with NULL should allow for proper counting.

    **Check out my Domo Tips & Tricks Videos

    **Make sure to <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.
  • Thanks Mark! We do want to show the N/As; is there a way to only calculate on the rows that have a number?

  • Unfortunately, not that I have seen. Would be a nice enhancement. I have seen workaround for this where you create a dashboard page and create some single value cards to show the counts you want and put them around your main card. The interactive filters will allow you to keep all cards in sync if someone filters on something. Not sure if building a dashboard page will work for your use case, but that is one idea for you.

    **Check out my Domo Tips & Tricks Videos

    **Make sure to <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.
  • Thanks again Mark!

  • Hi @ravimohan911

    CC @MarkSnodgrass

    So technically you could hack it a little bit to sum all of your values together while ignoring but it gets a bit wonky as the Total row is manually calculated and would need to be a separate row in your dataset. If the user does any sorting the Total row wouldn't be at the bottom anymore. You'd essentially conditionally display the total across the entire dataset using a window function if your key field = 'Total'.


    SUM will only add numerical values so it will automatically ignore your N/A values.


    CASE WHEN `Name` = 'Total' THEN SUM(SUM(`NA Numbers`)) OVER () ELSE `NA Numbers` END
    

    Raw Data:

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • Also, by utilizing a window function in a beast mode it'll properly handle any filtering done to the cards instead of doing it within an ETL.

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • Cheers. My table card is a drilldown, so I think I have to do it in Beast mode.