cancel
Showing results for
Did you mean:
Highlighted Helper II

## Help with Measure that can use multiple calcs

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

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted Helper II

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

3 REPLIES 3
Highlighted Helper II

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

Highlighted Microsoft

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

Highlighted Helper II

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  