Add a column to capture percent of total value for adjacent column

NathanDorsch
NathanDorsch Member
in Charting

I have a table set up below, which breaks down metrics by various column combinations (Campaign name/source/medium/content). Is there way I can add a column next to visitors to identify the % of the total visitors? So in % column, the value on the second row next to 107 would be 32% (107/330).

image.png


Answers

  • GrantSmith
    GrantSmith Coach

    Fixed function is your friend here.

    SUM(`Visitors`) / SUM(SUM(`Visitors`) FIXED ())

    https://domo-support.domo.com/s/article/4408174643607?language=en_US

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • NathanDorsch
    NathanDorsch Member

    This looks like it be a solution... but I don't know how to get it to work....

    'Visitors' is a beastmode calculation: COUNT(DISTINCT case when `URL without UTM` like 'https://climate.emerson.com/en-us/sustainability/heat-pump-technology' then `USER_PSEUDO_ID` end)

    How would you incorporate that into your proposed formula?

  • NathanDorsch
    NathanDorsch Member

    I tried this... and it isn't working. COUNT(DISTINCT case when `URL without UTM` like 'https://climate.emerson.com/en-us/sustainability/heat-pump-technology' then `USER_PSEUDO_ID` end) / (COUNT(DISTINCT case when `URL without UTM` like 'https://climate.emerson.com/en-us/sustainability/heat-pump-technology' then `USER_PSEUDO_ID` end) FIXED ())

    See screenshot below... 330 is the total.

    image.png


  • GrantSmith
    GrantSmith Coach

    Using LIKE without a wildcard will match your string exactly. If you want to match the start of your URL with that string add % at the end of your URL string.

    LIKE 'https://climate.....%'

    With fixed functions you'll need to aggregate the aggregate because of how they function.

    Wrap your denominator with a SUM.

    COUNT(DISTINCT case when `URL without UTM` like 'https://climate.emerson.com/en-us/sustainability/heat-pump-technology' then `USER_PSEUDO_ID` end)
/
SUM(COUNT(DISTINCT case when `URL without UTM` like 'https://climate.emerson.com/en-us/sustainability/heat-pump-technology' then `USER_PSEUDO_ID` end) FIXED ())
    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • GrantSmith
    GrantSmith Coach

    Another method I like to employ is to utilize a data table and display the numerator and denominator in different columns / beast modes to help determine if each are being calculated as expected.

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • NathanDorsch
    NathanDorsch Member

    I tried the formula you provided and am still getting the same results.

  • GrantSmith
    GrantSmith Coach

    If you split out the Numerator and Denominator into their own beast modes what values are you getting?

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**

Categories