cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper II
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.

DaxHelpExampleImage.jpg

 

 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
Helper II

Re: Help with Measure that can use multiple calcs

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'.

 

DaxHelpResults.jpg

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

 

View solution in original post

3 REPLIES 3
Highlighted
Helper II
Helper II

Re: Help with Measure that can use multiple calcs

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
Microsoft

Re: Help with Measure that can use multiple calcs

Hi @Rich_P,

 

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

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

r2.PNG

 

Regards

Highlighted
Helper II
Helper II

Re: Help with Measure that can use multiple calcs

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'.

 

DaxHelpResults.jpg

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

 

View solution in original post

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Upcoming Events

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors