help with margin formula

meadow_ryan
meadow_ryan ⚪️
in Dataflows

Im running the following formula to produce my margin %, and want when 'override_payee_id' = 'MEADBIMT' to have the % always be 15% but currently the way its written its taking .15/SUM(IFNULL(`total_charge`,0))*100 which isn't producing the correct amount, how can I write the following to have when the payee id is MEADBIMT to always be 15% but else run through the rest of my case statement

 

thanks

 

SUM(CASE

when `override_payee_id` = 'MEADBIMT' then .15
when IFNULL(`NewAmount`,0) = 0 then `total_charge` - `override_pay_amt`
ELSE `total_charge` - `override_pay_amt` - `NewAmount`

End

) / SUM(IFNULL(`total_charge`,0))*100

Best Answer

  • AS
    AS 🔵
    Answer ✓

    I think one of the parentheses is out of order.  And we can eliminate the *100 since the card can be formatted to improve readability.

     

    CASE
    WHEN `override_payee_id` = 'MEADBIMT'
    THEN AVG(.15)
    ELSE
    SUM(`total_charge` - `override_pay_amt` - IFNULL(`NewAmount`,0))
    /
    SUM(IFNULL(`total_charge`,0))
    END

    Aaron
    MajorDomo @ Merit Medical

    **Say "Thanks" by clicking the heart in the post that helped you.
    **Please mark the post that solves your problem by clicking on "Accept as Solution"

Answers

  • AS
    AS 🔵

    Try taking that case statement out of the division.  This might not work exactly as scripted, but use this for concept:

     

    CASE
    WHEN `override_payee_id` = 'MEADBIMT'

    THEN .15

    ELSE

    SUM(
    CASE 

    WHEN IFNULL(`NewAmount`,0) = 0

    THEN `total_charge` - `override_pay_amt`
    ELSE `total_charge` - `override_pay_amt` - `NewAmount`
    END
    )

    /

    SUM(IFNULL(`total_charge`,0))*100

    END

    Aaron
    MajorDomo @ Merit Medical

    **Say "Thanks" by clicking the heart in the post that helped you.
    **Please mark the post that solves your problem by clicking on "Accept as Solution"
  • meadow_ryan
    meadow_ryan ⚪️

    that didn't seem to change anything...I understand what you're saying about breaking apart the case statement but its like its still taking into account the case with the division even when the 'override_payee_id' = 'MEADBIMT'

  • AS
    AS 🔵

    There may be an issue with aggregated outputs versus nonaggregated outputs.  Aggregations in case statements can be tricky.

    We could simplify this a little bit also.

     

    CASE
    WHEN `override_payee_id` = 'MEADBIMT'

    THEN AVG(.15)

    ELSE

    (SUM( `total_charge` - `override_pay_amt` - IFNULL(`NewAmount`,0)
    /

    SUM(IFNULL(`total_charge`,0))*100)

    END

    Aaron
    MajorDomo @ Merit Medical

    **Say "Thanks" by clicking the heart in the post that helped you.
    **Please mark the post that solves your problem by clicking on "Accept as Solution"
  • meadow_ryan
    meadow_ryan ⚪️

    so I wrote as below, added an ending parenthesis as it was giving a syntax error if not, but when adding to the card is stating "An Issue occurred during processing, we are unable to complete the request at this time"

     

    CASE
    WHEN `override_payee_id` = 'MEADBIMT'

    THEN AVG(.15)

    ELSE

    (SUM( `total_charge` - `override_pay_amt` - IFNULL(`NewAmount`,0)
    /

    SUM(IFNULL(`total_charge`,0))*100))

    END

  • meadow_ryan
    meadow_ryan ⚪️

    that worked...thanks again

Categories