How do I calculate a row total?

ravimohan911
ravimohan911 Member
in Charting

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

image.png


Tagged:

Answers

  • MarkSnodgrass
    MarkSnodgrass Coach

    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.
  • ravimohan911
    ravimohan911 Member

    Thanks Mark! We do want to show the N/As; is there a way to only calculate on the rows that have a number?

  • MarkSnodgrass
    MarkSnodgrass Coach

    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.
  • ravimohan911
    ravimohan911 Member

    Thanks again Mark!

  • GrantSmith
    GrantSmith Coach

    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
    Screen Shot 2021-04-08 at 3.56.12 PM.png

    Raw Data:

    Screen Shot 2021-04-08 at 3.56.23 PM.png
    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • GrantSmith
    GrantSmith Coach

    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!**
  • ravimohan911
    ravimohan911 Member

    Cheers. My table card is a drilldown, so I think I have to do it in Beast mode.

Categories