Need help to return multiple calculations based on several different conditions

[Reine]

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:  

Allowable = IF(ISBLANK('report Charge'[Medicare Allowable Chg]),'report Charge'[TotalChargeAmount], IF(ISBLANK(RELATED('custom Patient'[InsPrimaryInsuranceCompanyName])),'report Charge'[TotalChargeAmount],'report Charge'[Medicare Allowable Chg]*'report Charge'[% of Medicare Allowable]))

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.

So in the above sample data, Bill 510320 has 8 "ChargeCodeID" rows - it should pick one of the charges of 1082.53 and kick back 77% of the "Medicare Allowable Chg" for it, and all the rest of the rows should kick back 50% of that 77%.  

Bill 506805 has only one ChargeCodeID, so it would be subject to the other conditions in my original If statements only.

I hope this makes sense, please let me know if further info is needed.  Thank you 🙂

[Reine]

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 🙂

[Reine]

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

[DataInsights · Accepted solution]

@Reine,

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]
)

[Reine]

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