Beastmode Percentage Calculation

user01052
user01052 Member
in Magic ETL

Hi There,

 

I'd like to do a percentage calucation of the following beast mode. 

 

COUNT(DISTINCT (CASE WHEN `Campaign` = 'A' THEN `ID` END)) / SUM(COUNT(DISTINCT (CASE WHEN `Campaign` = 'A' THEN `ID` END)))

 

Can you advise on how to do an embeded beast mode calcuation?

Thanks!

Best Answer

  • Property_Ninja
    Property_Ninja Contributor
    Answer ✓

    So there are two ways to do this, the first way would be to add a subtotal to your table card. It would look something like the picture attached. The second way would be to create a new aggregate column. Something like ... 

     

    select Campaign
    ,count(distinct id) as campaign_total
    from yourTable
    group by Campaign

    Then you would join this to your campaign rows on Campaign in your main table and create a beastmode like ... 

    COUNT(DISTINCT CASE WHEN `Campaign` = 'A' THEN `ID` END)
    /
    MAX(campaign_total)

     

    Hope this helps,

     

    Brian


    **Please mark "Accept as Solution" if this post solves your problem
    **Say "Thanks" by clicking the "heart" in the post that helped you.

Answers

  • Property_Ninja
    Property_Ninja Contributor

    Hi,

     

    I am not entirely sure what you are trying to accomplish here. Can you elaborate, maybe providing some examples with numbers?

     

    Thanks,

     

    Brian


    **Please mark "Accept as Solution" if this post solves your problem
    **Say "Thanks" by clicking the "heart" in the post that helped you.
  • user01052
    user01052 Member

    Hi,

     

    This beastmode is how i'm getting the metric for active campaigns. 

    COUNT(DISTINCT (CASE WHEN `Campaign` = 'A' THEN `ID` END))

     

    the output is the attachment below.

    I'd like to take the percentage of each row after summing the entire active campaigns column. 

    For Auto/dealer, I'd like 724.4/2088.6 = 34.6%

     

    Hopefully that helps!

  • Property_Ninja
    Property_Ninja Contributor
    Answer ✓

    So there are two ways to do this, the first way would be to add a subtotal to your table card. It would look something like the picture attached. The second way would be to create a new aggregate column. Something like ... 

     

    select Campaign
    ,count(distinct id) as campaign_total
    from yourTable
    group by Campaign

    Then you would join this to your campaign rows on Campaign in your main table and create a beastmode like ... 

    COUNT(DISTINCT CASE WHEN `Campaign` = 'A' THEN `ID` END)
    /
    MAX(campaign_total)

     

    Hope this helps,

     

    Brian


    **Please mark "Accept as Solution" if this post solves your problem
    **Say "Thanks" by clicking the "heart" in the post that helped you.

Categories