Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Rich_P
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

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
Rich_P
Helper II
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")
)
)

 

 

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

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

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.