calculate percentage difference between 2 values which is in a column

nica_tc
nica_tc Member
in Charting

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.

data_structure.png

and next step i make a pivot table and segregate the "monthly_balance" by "source" (actual and budget)

pivot table.png

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.

difference.png


not work.png

is there anyone could provide me some suggestion?

Answers

  • mhouston
    mhouston Contributor

    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
    RobSomers Coach

    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!**

  • nica_tc
    nica_tc Member
    edited 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

    image.png

    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.

    image.png

    may i know is there anything i do wrong?

    image.png


  • RobSomers
    RobSomers Coach

    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!**

  • nica_tc
    nica_tc Member
    edited July 2022

    hi @RobSomers,


    the closing parentheses is already included. the screenshot didn't include it only.

    image.png


Categories