calculate percentage difference between 2 values which is in a column

nica_tc · July 2022

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?

mhouston · July 2022

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.

RobSomers · July 2022

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.

nica_tc · July 2022

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.