calculate percentage difference between 2 values which is in a column
hi all,
i am new to domo and would like to seek your kindly advise regarding on my question.
i make a ETL and the structure is like below.
and next step i make a pivot table and segregate the "monthly_balance" by "source" (actual and budget)
the next step is i want to calculate the "monthly_balance" percentage difference between the 'actual' and 'budget' and attached in table last column
for example the first line would be ((52935094.74-52746575.11)/(52935094.74))*100% = 0.356%
same calculation is apply for the rest of lines.
i try to make a beast mode calculation for this purpose but seem not work and not fit my expectation.
is there anyone could provide me some suggestion?
Answers
-
I think it's not working in your pivot table as laid out because you have the difference column underneath your larger categories of ACTUAL and BUDGET (so in the ACTUAL - difference, the only data the beast mode has is actuals).
The way we've implemented this is using an HTML table and just creating a beast mode for actuals, a beast mode for budget, and a beast mode for variance.
ACTUALS:
sum(
case when `version` = 'Actuals' then `account_value`
ELSE 0
end)
BUDGET:
sum(
case when `version` = 'Budget' then `account_value`
ELSE 0
end)
Variance:
(sum(
case when `version` = 'Actuals' then `account_value` else 0
end)
-
sum(
case when `version` = 'Budget' then `account_value` else 0
end))/
sum(
case when `version` = 'Budget' then `account_value` else 0
end)
Note that the variance calc here is actually a ratio, and then we use the domo formatting to format it to a percent.
3 -
I agree with @mhouston. Your original beast mode with the sum inside the case when statements just does the calculation on a line by line basis in your base table. If you want to do a sum or any calculation based on your table as a whole, then you have to put your case when inside the SUM function.
**Was this post helpful? Click Agree or Like below**
**Did this solve your problem? Accept it as a solution!**
3 -
hi @mhouston and @RobSomers,
thank you for the beast mode suggestion. I adjust my beast mode calculation and applied on the pivot table.
the result is really fit my expected but i still got some issue on it.
issue 1: dun know why there is an 50% difference shown in below.
it is really tricky if i make a html table and applied those 3 beast mode calculation for actual_sum, budget_sum and difference. the 50% still shown and shown 0 value for both actual_sum and budget_sum
issue 2: unexpected 0 shown and some sub-total go wrong when I see more detail. the difference is not able to shown if "categories" this field is applied.
may i know is there anything i do wrong?
0 -
It looks like your last sum function is missing a closing parentheses after 'else 0'
**Was this post helpful? Click Agree or Like below**
**Did this solve your problem? Accept it as a solution!**
0 -
0
Categories
- 10.5K All Categories
- 8 Connect
- 918 Connectors
- 250 Workbench
- 470 Transform
- 1.7K Magic ETL
- 69 SQL DataFlows
- 477 Datasets
- 194 Visualize
- 253 Beast Mode
- 2.1K Charting
- 11 Variables
- 17 Automate
- 354 APIs & Domo Developer
- 89 Apps
- 3 Workflows
- 20 Predict
- 5 Jupyter Workspaces
- 15 R & Python Tiles
- 247 Distribute
- 63 Domo Everywhere
- 243 Scheduled Reports
- 21 Manage
- 42 Governance & Security
- 174 Product Ideas
- 1.2K Ideas Exchange
- 12 Community Forums
- 27 Getting Started
- 14 Community Member Introductions
- 55 Community News
- 4.5K Archive