Help with Measure that can use multiple calcs

[Rich_P]

I have another challenge to share in case anyone is feeling inspired.

The image below is a PowerPivot file built from a single table in the data model.

 In this image, I've circled where I am missing a calc, and I placed the "X" where I shouldn't have a calc.

My challenge is to calculate the Recovery Rate for
individual records AND
sub totals AND
Site Totals AND
State Totals AND
Grand Total.

I know what you’re thinking…no sweat…but I don’t think it’s as simple as it seems.
There are 2 potential calculations that could give the Rate depending on some conditions
on that row (I know: context!).

Case 1:
Every record where the Submittal Number is a whole number
(these records always happen to have Appealed = $0),
the Recovery Rate is calculated by Approval / Claimed

Case 2:
Every record where the Submittal Number is NOT a whole number
(these records always happen to have Claimed = $0),
the Recovery Rate is calculated by Appealed / Claimed.

Simple, No? But wait, there’s more…

For ALL subtotals, totals and grand totals, the Rate uses Approval / Claimed (same as Case 1).

Oh, one more thing, We only want to calculate these Rates for records
where the [Submittal Status] is “Approved” or “Paid” or “Denied”.
Good one, huh?

I have included a link to my example file which shows my work so far.
I've tried a number of things that almost work.
For instance, I thought this was going to work:

Claim_Recovery_Rate:=if(HASONEVALUE(ExcelModel[Submittal Number]),
sumx(
Filter(ExcelModel,
(ExcelModel[Submittal Status]="Approved") ||
(ExcelModel[Submittal Status]="Paid") ||
(ExcelModel[Submittal Status]="Denied")
),
If(ExcelModel[AppealedAmt] >0,
Divide(ExcelModel[ApprovedAmt],ExcelModel[AppealedAmt]),
Divide(ExcelModel[ApprovedAmt],ExcelModel[ClaimedAmt])
)
),
BLANK()
)

But it just isn't doing everything I need.

Any takers? 

Have a great weekend.

Thanks,

Rich P

[Rich_P]

I'm still looking for help, but I tried to think about it a different way in the hope that I could figure it out on my own. Here's the revised thinking (but the rules are still the same):

\\ The first IF statement lets me use a specific calc for the totals rows
\\ while I use additional branching to determine which calc to use fo individual records

IF HASONEVALUE(ExcelModel[Site]) , 

      \\ This IF statement allows me to display a calc on only the rows where the 
      \\ Submittal Status are one of the three mentioned, and a BLANK() otherwise

      IF ExcelModel[Submittal Status] = "Approved" or
                           ExcelModel[Submittal Status] = "Paid" or  

                           ExcelModel[Submittal Status] = "Denied"    THEN

   
            \\ This IF statement chooses which formula to use based on the value of the AppealedAmt field

            IF ExcelModel[AppealedAmt] > 0 THEN

                 [fApproved] / [fAppealed],

            ELSE    

                 [fApproved] / [fClaimed]

            END IF

       ELSE

            BLANK()

ELSE

       [fApproved] / [fClaimed]

END IF

WHERE the following measures exist:

fClaimed:=CALCULATE(sum(ExcelModel[ClaimedAmt]),
Filter(ExcelModel,
(ExcelModel[Submittal Status]="Approved") ||
(ExcelModel[Submittal Status]="Paid") ||
(ExcelModel[Submittal Status]="Denied")
)
)

fApproved:=CALCULATE(SUM(ExcelModel[ApprovedAmt]),
Filter(ExcelModel,
(ExcelModel[Submittal Status]="Approved") ||
(ExcelModel[Submittal Status]="Paid") ||
(ExcelModel[Submittal Status]="Denied")
)
)

fAppealed:=CALCULATE(SUM(ExcelModel[AppealedAmt]),
Filter(ExcelModel,
(ExcelModel[Submittal Status]="Approved") ||
(ExcelModel[Submittal Status]="Paid") ||
(ExcelModel[Submittal Status]="Denied")
)
)

[v-ljerr-msft]

Hi @Rich_P,

Based on my test, the formulas below should work in your scenario. 

Recovery Rate 22:=IF (
    HASONEVALUE ( ExcelModel[Site] ),
    IF (
        FIRSTNONBLANK(ExcelModel[Submittal Status],1) = "Approved"
            || FIRSTNONBLANK(ExcelModel[Submittal Status],1)= "Paid"
            || FIRSTNONBLANK(ExcelModel[Submittal Status],1) = "Denied",
        IF (
            MAX(ExcelModel[AppealedAmt]) > 0,
            [fApproved] / [fAppealed],
            [fApproved] / [fClaimed]
        ),
        BLANK ()
    ),
    [fApproved] / [fClaimed]
)

Recovery Rate 33:=AVERAGEX(ExcelModel,[Recovery Rate 22])

Regards

[Rich_P · Accepted solution]

Thanks for the reply v-ljerr-msft - 

Unfortunately, this doesn't quite do what I need it to do. But you have piqued my curiosity with your use of FIRSTNONBLANK(). I don't think I have seen that used anywhere before.

Your solution is taking the average of percentages which doesn't work out in this case. For example, for the RI  Submittals 5 & 5.11 we have Recovery rates calculated as 67.52% and 46.97% respectively. For the Site, you calc the average of them as 57.25%. The correct answer is really 75.52%  (22,614.48 / 29.944.45).

I toiled over this over the weekend and believe I came up with my own answer. I did use the 3 measures mentioned in my previous post (fClaimed, fApproved & fAppealed). And I tied it all together with this measure:

Recovery Rate:=IF(HASONEVALUE(ExcelModel[Submittal Number]),

	
	CALCULATE(
		IF(ExcelModel[Appealed]>0,
			DIVIDE([fApproved],[fAppealed]),
			DIVIDE([fApproved],[fClaimed])
			),
			Filter(ExcelModel, 
		(ExcelModel[Submittal Status]="Approved") ||
		(ExcelModel[Submittal Status]="Paid") ||
		(ExcelModel[Submittal Status]="Denied")
		)
			) ,
			
	CALCULATE(
		DIVIDE([fApproved],[fClaimed])
			,
			Filter(ExcelModel, 
		(ExcelModel[Submittal Status]="Approved") ||
		(ExcelModel[Submittal Status]="Paid") ||
		(ExcelModel[Submittal Status]="Denied")
		)
			)			
			)

This measure handles the totals properly, filtering out the claimed amounts for Submittal statuses of 'Submitted'.

But I really appreciate you taking the time to take a shot at it. I'm sure I'll have more as I dive in over my head!

Thanks,

Rich P