SubTotal Category Remaining Amount: How to calculate difference between two columns

How to display the value of "Remaining Amount' in subtotal category:

I have the following scenario, using a Pivot table Displaying the budget amount and actual amount along with remaining amount.

for every subcategory I do not want to display remaining amount if either value budget or Actual value is 0.

However I will still like to display a value for remaining amount if either the Budget or Actual value exist (My understanding is that I the value rolls up and adds to display subtotal values).

Below is the formula's I used to calculate Remaining Amount in BeastMode calculation.

CASE

when 

sum(case when `Transaction_Type` = 1 then `Amount` else 0 end) =0

or

sum(case when `Transaction_Type` = 0 then `Amount` else 0 end) =0

then 0

else

sum(case when `Transaction_Type` = 1 then `Amount` else 0 end)

-

sum(case when `Transaction_Type` = 0 then `Amount` else 0 end)

end

Thank you

~Monika

Answers

  • rahul93
    rahul93 NY 🟠

    Hi,

    Instead of 0 in your code can you put '' in the beast mode. So if budget or actual value is 0 then '' .

    Does that give you what you need?


    Thanks

  • GrantSmith
    GrantSmith Indiana 🟤

    Hi @msharma

    Because the way pivot tables behave and the fact that subtotals are an all or nothing setting what you're looking to do isn't possible at this time.

  • jaeW_at_Onyx
    jaeW_at_Onyx Budapest / Portland, OR 🟤

    @msharma ,

    @rahul93 is partially correct, if you put a '' in your ELSE clause you will convert your beastmode into a string. It therefore wouldn't format or behave like a number after that point. probably not desireable.

    To get 'nothing' as Rahul is recommending, you can however use ELSE NULL


    sum(case when `Transaction_Type` = 1 then `Amount` else null end) 
    -- or
    sum(case when `Transaction_Type` = 1 then `Amount` end) =0
    


    Now that said you have to be careful with null

    if you take

    5 - NULL
    
    

    the result witll be NULL

    @GrantSmith talks about this at the IDEAS Exchange Conference, https://www.youtube.com/watch?v=gO8OLpsAk4M&list=PLUy_qbtzH0S6-5oDbx3BsIv2Xk-JxJxWi&index=6


    SOLUTION

    test if your amounts net to 0 if so replace with null

    case 
    when sum(case when ... then amount else 0 end) - sum(case when ... then budget else 0 end) = 0 then null 
    else sum(case when ... then amount else 0 end) - sum(case when ... then budget else 0 end)
    end
    


    THAT SAID. WHAT YOU'RE ASKING FOR IS LOGICALLY INAPPROPRIATE

    Null and 0 are not the same thing.

    If i had 5 dollars and spent 5 dollars i have zero dollars left.

    That's different from I spent nothing and i had no budget therefore the value should be null.

  • rahul93
    rahul93 NY 🟠

    @jaeW_at_Onyx


    Hi, using a '' doesn't convert the field into a string field. I just tested it and it works as a numeric field. See screenshots



    Thanks

Sign In or Register to comment.

Hey, Stranger!

It looks like you're new here. Those who sign in get access to engage with even MORE fire content. To get involved, click one of these buttons!