Beast mode percentage of

user07534
user07534
in Magic ETL

I am trying to calculate the percentage of the total for each media. I will be using this on a table (media as dimention, spend as metrics). Here is my formula:

 

SUM(CASE WHEN `media` = 'Radio' THEN `spend`
WHEN `media` = 'A' THEN `spend`
WHEN `media` = 'B' THEN `spend`
WHEN `media` = 'C' THEN `spend`
END)
/
SUM(`spend`)

 

However, it seems the second does not SUM all spend. So, it gives 100% on each media.

 

How do I get them?

Please advice.

Comments

  • ST_-Superman-_
    ST_-Superman-_ Coach

    You could use a 100% stacked bar chart to show the percentages. 

     

    Actually, I’m pretty sure most bar graphs give a data label setting to show percent of total. 

     

     

    You could also use beast modes to display the information in a table horizontally. But you would need a separate bastmode for each metric. 

    Radio

    SUM(CASE WHEN `media` = 'Radio' THEN `spend`
    ELSE 0
    END)
    /
    SUM(`spend`)

    A

    SUM(CASE 
    WHEN `media` = 'A' THEN `spend`
    ELSE 0
    END)
    /
    SUM(`spend`)

    And so on...

     

    you our would have to make sure that `media` was not a field on your table though


    “There is a superhero in all of us, we just need the courage to put on the cape.” -Superman
  • user07534
    user07534

    Hi Superman,

     

    As you mentioned, each calculation and formula works. But. I want to show % on a table, not a chart, and  'media' dimension would be on the table.

     

    Do you know how to get these in one place?

Categories