Dividing with Multiple Beast Mode Functions
I have 2 formulas I am trying to divide into one another
CASE
WHEN `Action` = 'Lead' THEN SUM(`Unique Events`)
END
and
CASE
WHEN `Action` = 'Completion' THEN SUM(`Unique Events`)
END
Individually they work fine and result in 100000 and 50000 respectively. I need to divide Completions into Leads to determine my dropoff rate (50000/100000). Can someone please help me to divide these 2 Beast Mode functions?
Comments

Hi,
Please try this ,
SUM(CASE
WHEN `Action` = 'Lead' THEN `Unique Events`
END)
/
SUM(CASE
WHEN `Action` = 'Completion' THEN `Unique Events`
END)
So basically, type the first part but aggregate the entire case statement instead of aggregating it inside the case. Then divide it by the second formula, here also, please aggregate the entire case statement and not the inner 'then' or 'else' condition, just as I have shown in the above formula.
Also it's advised to use the aggregation function outside the case statement to avoid erros in aggregation.Hope this helps
Regards
Aditya Jain
1 
Thanks Aditya,
The function returns only blank rows now. Do you have any alternatives or edits to the function?
If not are there any alternative solutions you can recommend? I am just trying to divide among filtered aggregates.
0 
Hi,
The reason for it to return blank rows could be,
 There could be a spelling error in the 'WHEN' condition, so kindly check the spelling (it's case sensitive too).
 The condition is never true and since there is no ELSE condition, it would return null, so kindly add ELSE 0 in both the condition or give an appropriate ELSE condition if the WHEN is turning out to be false
for exampleSUM(CASE
WHEN `Action` = 'Lead' THEN `Unique Events` ELSE 0
END)
/
SUM(CASE
WHEN `Action` = 'Completion' THEN `Unique Events` ELSE 0
END)
 There could be null values in your columns you are using as the numerator and denominator. In that case, you can use IFNULL function to handle null by converting the null to 0. for example,
SUM(IFNULL(CASE
WHEN `Action` = 'Lead' THEN `Unique Events`
END,0))
/
SUM(IFNULL(CASE
WHEN `Action` = 'Completion' THEN `Unique Events`
END,0))
Do let me know if this helps.
Aditya Jain
1 
Thanks Again Aditya,
Those recommendations did get data into my columns, but they were still 0 unfortunately.
At this point, is Magic ETL a better option? I am not sure what the issue could be otherwise since I am pulling this data directly from analytics.
1 
Hi @user022663 ,
If you are still getting 0, then it could be because the numerator part of the formula is 0. So I think you should check the data for the numerator, i.e., 'Action' = 'Lead' part.. Also, please check if you are using your column 'Action' as a filter and you have not put 'Action' = 'Completion' as a filter. I think you are almost there to the solution
Yes, ETL is another option, but data pivoting will be required while this problem is not a big issue so beast modes are sufficient for such issues.
We can try ETL way as well, but for any suggestions, some more info regarding the data is needed which you may not be comfortable to share. If that's not the case you can inbox the sample data and we can solve this.Hope this helps.
Regards
Aditya Jain
0 
@user022663  Are you formatting the percentage correctly? If it's displaying a number and it's under 50% (0.5) it'll round down being displayed as 0. Make sure your field is being formatted as a percentage and check the multiply by 100 box (and even add a decimal point or two)
**Was this post helpful? Click Agree or Like below**
**Did this solve your problem? Accept it as a solution!**1
Categories
 10.6K All Categories
 1 APAC User Group
 12 Welcome
 36 Domo News
 9.6K Using Domo
 1.9K Dataflows
 2.4K Card Building
 2.2K Ideas Exchange
 1.2K Connectors
 339 Workbench
 252 Domo Best Practices
 11 Domo Certification
 461 Domo Developer
 47 Domo Everywhere
 100 Apps
 703 New to Domo
 84 Dojo
 Domopalooza
 1.1K 日本支部
 4 道場日本支部へようこそ
 22 お知らせ
 63 Kowaza
 296 仲間に相談
 649 ひらめき共有