HELP CENTER

HELP CENTER

Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Dojo
- :
- Using Domo
- :
- Beast Mode, ETL/Dataflow
- :
- Re: Beast Mode % of the total in Chart

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Notify Moderator

06-29-2020
05:56

06-29-2020
05:56

I want to create a calculated value that show the % of the total. I have done this:

SUM(Case WHEN `Country` LIKE '%In' then `Value` ELSE 0 END)

/

SUM(Case WHEN `Country` = 'TotalIns' then `Value` ELSE 0 END)

But it displays anything although the formula is correct. I tried using only the first part of the formula and it works, I don't understand what's wrong.

Accepted Solutions

Highlighted

**Was this post helpful? Click the heart icon**

**Did this solve your problem? Accept it as a solution!**

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Notify Moderator

06-29-2020
08:47

06-29-2020
08:47

Have you tried just the denominator (second part) and see what that returns? Based on your formula I'm assuming you have another row in your table which has the overall total, is that correct?

You could also attempt to do a windowed function (assuming you have them enabled in your instance - if not talk to your CSM to get them enabled).

```
SUM(CASE WHEN `Country` LIKE '%In' THEN `Value` ELSE 0 END)
/
SUM(SUM(CASE WHEN `Country` LIKE '%In' THEN `Value` ELSE 0 END)) OVER ()
```

Edit: Late night Dojo surfing = poor code. As @jaeW_at_Onyx mentioned you need sum(sum(...)). The code has been updated to reflect the correct syntax.

**Was this post helpful? Click the heart icon**

**Did this solve your problem? Accept it as a solution!**

All Replies

Highlighted

**Was this post helpful? Click the heart icon**

**Did this solve your problem? Accept it as a solution!**

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Notify Moderator

06-29-2020
08:47

06-29-2020
08:47

Have you tried just the denominator (second part) and see what that returns? Based on your formula I'm assuming you have another row in your table which has the overall total, is that correct?

You could also attempt to do a windowed function (assuming you have them enabled in your instance - if not talk to your CSM to get them enabled).

```
SUM(CASE WHEN `Country` LIKE '%In' THEN `Value` ELSE 0 END)
/
SUM(SUM(CASE WHEN `Country` LIKE '%In' THEN `Value` ELSE 0 END)) OVER ()
```

Edit: Late night Dojo surfing = poor code. As @jaeW_at_Onyx mentioned you need sum(sum(...)). The code has been updated to reflect the correct syntax.

**Was this post helpful? Click the heart icon**

**Did this solve your problem? Accept it as a solution!**

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Notify Moderator

06-30-2020
02:00

06-30-2020
02:00

Sorry @GrantSmith 😛 i know you're gunning for that belt 😛

```
SUM(CASE WHEN `Country` LIKE '%In' THEN `Value` ELSE 0 END)
/
SUM(CASE WHEN `Country` LIKE '%In' THEN `Value` ELSE 0 END) OVER ()
```

to get to work in Domo, the denominator would have to be:

sum(sum( case ... end )) over ()

@nat040711 , if you haven't seen windowed functions before, I did a tutorial using LAG and LEAD, but SUM(SUM()) over is covered as well.

https://www.youtube.com/watch?v=cnc6gMKZ9R8&list=PLUy_qbtzH0S4CkHBUvpOVpLNJluk6upOn&index=23&t=1s

Jae Wilson

Check out my Domo Training YouTube Channel

**Say "Thanks" by clicking the heart in the post that helped you.

**Please mark the post that solves your problem by clicking on "Accept as Solution"

How to create lag functions in Card #Analyzer using #BeastModes ##### CONSULTING SERVICES #### I have left Domo to start my own consulting company, #OnyxRepo...

Announcements