Beast Mode % of the total in Chart

nat040711
nat040711
in Dataflows

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.

Best Answer

  • GrantSmith
    GrantSmith Indiana 🥷
    Answer ✓

    @nat040711,

    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 Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**

Answers

Categories