Forum Discussion
Need help to return multiple calculations based on several different conditions
Hello - I have run into a problem that is beyond my knowledge and would appreciate any help you can give.
I have a table with data for surgeries performed per day. With this data I am calulating total cost for each surgery, then using that data and some data from other tables to determine what their insurance will allow to be paid for that surgery. Currently I have a calculated column set up in my table with an IF statement that is working well for the simple conditions I needed. But now I have been asked to add in some other conditions and I can't figure it out.
My table looks like this, the last 3 columns are calculated columns:
My current calculated column, "Allowable" looks for blanks in certain areas and returns one thing if there are blanks, and another for everything else:
It turns out that if there is more than one "ChargeCodeID" per "BillID", then insurance will pay the percentage in the "% of Medicare Allowable" column for ONLY the most expensive part of the surgery - the rest are paid at 50% of that. So what I need to do now is add in another IF statement (or do something else entirely) to my "Allowable" column that not only looks at the current conditions but also checks if the "Bill ID" has more than one "ChargeCode ID", and if it does, find the one with the highest charge and multiple it by "% of Medicare Allowable" and the rest of them at 50% of the percentage in the "% of Medicare Allowable" column. Often there will be several with the same Charge amount, and in that case it doesn't matter which it picks, it just has to be one of highest.
I hope this makes sense, please let me know if further info is needed. Thank you 🙂
Try this calculated column:
Allowable = SWITCH ( TRUE (), // scenario 1 ISBLANK ( 'report Charge'[Medicare Allowable Chg] ) || ISBLANK ( RELATED ( 'custom Patient'[InsPrimaryInsuranceCompanyName] ) ), 'report Charge'[TotalChargeAmount], // scenario 2 CALCULATE ( COUNTROWS ( 'report Charge' ), ALLEXCEPT ( 'report Charge', 'report Charge'[BillID] ) ) > 1, VAR vMaxAmount = CALCULATE ( MAX ( 'report Charge'[TotalChargeAmount] ), ALLEXCEPT ( 'report Charge', 'report Charge'[BillID] ) ) VAR vMinChargeIDForMaxAmount = CALCULATE ( MIN ( 'report Charge'[ChargeID] ), ALLEXCEPT ( 'report Charge', 'report Charge'[BillID] ), 'report Charge'[TotalChargeAmount] = vMaxAmount ) RETURN SWITCH ( TRUE (), 'report Charge'[ChargeID] = vMinChargeIDForMaxAmount, 'report Charge'[TotalChargeAmount] * [% of Medicare Allowable], 'report Charge'[ChargeID] <> vMinChargeIDForMaxAmount, 'report Charge'[TotalChargeAmount] * [% of Medicare Allowable] * .5 ), // scenario 3 'report Charge'[Medicare Allowable Chg] * 'report Charge'[% of Medicare Allowable] )
4 Replies
Here is the sample data pasted instead of a screen shot. This is the 'report Charge' table. Let me know if I still haven't done this correctly:
ChargeID Date BillID ChargeCodeID CPTModifier1Description OrderingProviderID PatientInternalID TotalChargeAmount % of Medicare Allowable Medicare Allowable Chg Allowable 2582406 01/04/21 506805 139791 Surgery 278985 122161 1,375.38 100% 1082.33 1,082.33 2600003 01/04/21 510320 139873 Left upper lid 374208 385842 304.35 77% 258 198.66 2600002 01/04/21 510320 133858 Left lower lid 374208 385842 1,082.53 77% 888.92 684.47 2600001 01/04/21 510320 133858 Left upper lid 374208 385842 1,082.53 77% 888.92 684.47 2600000 01/04/21 510320 139874 Left lower lid 374208 385842 1,082.53 77% 888.92 684.47 2599999 01/04/21 510320 139874 Left upper lid 374208 385842 1,082.53 77% 888.92 684.47 2599998 01/04/21 510320 139905 Left lower lid 374208 385842 1,082.53 77% 888.92 684.47 2599997 01/04/21 510320 139905 Left upper lid 374208 385842 1,082.53 77% 888.92 684.47 And here is a sample of the other table referenced in my "allowable" column
PatientInternalID InsPrimaryInsuranceCompanyName 122161 Regence BCBS MedAdvantage 385842 OHP AllCare Health Plan CCO 249147 Try this calculated column:
Allowable = SWITCH ( TRUE (), // scenario 1 ISBLANK ( 'report Charge'[Medicare Allowable Chg] ) || ISBLANK ( RELATED ( 'custom Patient'[InsPrimaryInsuranceCompanyName] ) ), 'report Charge'[TotalChargeAmount], // scenario 2 CALCULATE ( COUNTROWS ( 'report Charge' ), ALLEXCEPT ( 'report Charge', 'report Charge'[BillID] ) ) > 1, VAR vMaxAmount = CALCULATE ( MAX ( 'report Charge'[TotalChargeAmount] ), ALLEXCEPT ( 'report Charge', 'report Charge'[BillID] ) ) VAR vMinChargeIDForMaxAmount = CALCULATE ( MIN ( 'report Charge'[ChargeID] ), ALLEXCEPT ( 'report Charge', 'report Charge'[BillID] ), 'report Charge'[TotalChargeAmount] = vMaxAmount ) RETURN SWITCH ( TRUE (), 'report Charge'[ChargeID] = vMinChargeIDForMaxAmount, 'report Charge'[TotalChargeAmount] * [% of Medicare Allowable], 'report Charge'[ChargeID] <> vMinChargeIDForMaxAmount, 'report Charge'[TotalChargeAmount] * [% of Medicare Allowable] * .5 ), // scenario 3 'report Charge'[Medicare Allowable Chg] * 'report Charge'[% of Medicare Allowable] )
Oh my goodness - you are a life saver! Thank you! This works perfectly. This is defintely over my head, though I do understand at least part if what is going on. I guess I am going to need to research SWITCH. Thanks again, I appreciate it very much 🙂
