Forum Discussion

Rich_P's avatar
Rich_P
Helper II
9 years ago
Solved

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...
Need Help
  • Rich_P's avatar
    Rich_P
    9 years ago

    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

     

v-ljerr-msft's avatar
v-ljerr-msft
Microsoft Employee
9 years ago

Hi Rich_P,

 

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

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's avatar
Rich_P
Helper II
9 years ago

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