How do I calculate a row total?

ravimohan911
ravimohan911 Montréal ⚪️

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


Tagged:

Answers

  • MarkSnodgrass
    MarkSnodgrass Portland, Oregon 🟤

    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.

  • ravimohan911
    ravimohan911 Montréal ⚪️

    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 Portland, Oregon 🟤

    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.

  • ravimohan911
    ravimohan911 Montréal ⚪️

    Thanks again Mark!

  • GrantSmith
    GrantSmith Indiana 🟤

    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:

  • GrantSmith
    GrantSmith Indiana 🟤

    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.

  • ravimohan911
    ravimohan911 Montréal ⚪️

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

Sign In or Register to comment.

Hey, Stranger!

It looks like you're new here. Those who sign in get access to engage with even MORE fire content. To get involved, click one of these buttons!