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
'Happy to Help'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
'Happy to Help'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
'Happy to Help'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
 8 Connect
 918 Connectors
 250 Workbench
 477 Transform
 1.8K Magic ETL
 69 SQL DataFlows
 478 Datasets
 218 Visualize
 260 Beast Mode
 2.1K Charting
 12 Variables
 19 Automate
 356 APIs & Domo Developer
 89 Apps
 3 Workflows
 20 Predict
 5 Jupyter Workspaces
 15 R & Python Tiles
 249 Distribute
 65 Domo Everywhere
 243 Scheduled Reports
 21 Manage
 42 Governance & Security
 191 Product Ideas
 1.2K Ideas Exchange
 11 Community Forums
 27 Getting Started
 14 Community Member Introductions
 55 Community News
 4.5K Archive