Creating a calculated column in beast mode results in data being split up

DomoNewbie

Hi all - I am new to Domo and experiencing some issues displaying desired results in a bullet chart.

I have a beast mode calculation where it provides a value if one criteria is met, and another when a different criteria is met. In my bullet bar graph, when I select both criterias in the filters, values are now split into two sets where I'm expecting one consolidated total.

I also tried capturing both criterias in the case statement but it still displayed both sets. Am I writing the beast calculation incorrectly or should I be creating this formula in the ETL?

Bullet chart - results

Bullet chart - desired results

MarkSnodgrass

Try removing the SUM functions in your beast mode and then in your target value change the aggregation type to SUM. I played around with the different variations and I was able to duplicate your issue when I added the SUM functions within the beast mode. Taking them out and using the SUM function in the aggregation type took away the duplication on the x-axis.

MarkSnodgrass

Is there anything in your Sorting properties? I'm wondering if LOB is in your Sorting properties and that is causing the x-axis to be repeated.

DomoNewbie

There are currently no sorts being applied.

MarkSnodgrass

Try removing the SUM functions in your beast mode and then in your target value change the aggregation type to SUM. I played around with the different variations and I was able to duplicate your issue when I added the SUM functions within the beast mode. Taking them out and using the SUM function in the aggregation type took away the duplication on the x-axis.

DomoNewbie

@MarkSnodgrass the suggestion you have provided worked. I moved the aggregation from beast mode to the ETL/dataflow (via Add Formula in the Group By) and the values are not split anymore! Much thanks for your help!

jaeW_at_Onyx

@DomoNewbie /@MarkSnodgrass

you generally shouldn't put an aggregate function inside a CASE statement.

the reason why your final solution works is b/c by using the aggregate functions in Analyzer, functionally you're moving the aggregate out of the CASE statement.

you could write your beast mode as:

sum(
CASE
WHEN `LOB` = 'WLS' and `L2` = 'INBOUND' THEN ...
...
WHEN `LOB` = 'WLN' and `L2` = 'INBOUND' THEN ...
END
)

I always recommend writing the aggregation into the beast mode if appropriate because it forces people to use the metric 'the right way'.

ex. sometimes it's inappropriate to use AVG() or SUM() or MAX() so might as well build a beast mode for that.