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
- All Categories
- 1.8K Product Ideas
- 1.8K Ideas Exchange
- 1.5K Connect
- 1.2K Connectors
- 300 Workbench
- 6 Cloud Amplifier
- 9 Federated
- 2.9K Transform
- 100 SQL DataFlows
- 620 Datasets
- 2.2K Magic ETL
- 3.9K Visualize
- 2.5K Charting
- 742 Beast Mode
- 58 App Studio
- 41 Variables
- 686 Automate
- 176 Apps
- 453 APIs & Domo Developer
- 47 Workflows
- 10 DomoAI
- 36 Predict
- 15 Jupyter Workspaces
- 21 R & Python Tiles
- 395 Distribute
- 113 Domo Everywhere
- 276 Scheduled Reports
- 6 Software Integrations
- 124 Manage
- 121 Governance & Security
- 8 Domo Community Gallery
- 38 Product Releases
- 10 Domo University
- 5.4K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 108 Community Announcements
- 4.8K Archive