Forum Discussion
Weighted Reimbursement Formula
Ah, so since other Locations can borrow against each other under the same vendor, the expression becomes simpler:
Reimbursement = IF(SUM('Table'[adjustments])+SUM('Table[allowance]) > 0, 0, SUM('Table'[adjustments])+SUM('Table'[allowance]) ) )This has the issue of letting vendors "borrow" against each other in the same way for the grand total, but we can fix that by combining it with the original solution and grouping at the Vendor level. I've also added in a *-1 here because your examples have reimbursement as a positive value.
Reimbursement = SUMX( VALUES('Table'[Vendor]), CALCULATE(IF(SUM('Table'[adjustments])+SUM('Table'[allowance]) > 0, 0, (SUM('Table'[adjustments])+SUM('Table'[allowance]))*-1 )) )Now we start getting really tricky in order to get the weighted reimbursement in the same measure that calculates the correct amount at the Vendor level or higher. Luckily, we already have a working [Reimbursement] measure, so we're going to re-use that. I use DIVIDE instead of the normal division symbol here because it seems likely that the sum of adjustments can be zero, which causes errors. DIVIDE handles division by zero errors much more gracefully than the / symbol does.
Weighted Reimbursement = DIVIDE(SUM('Table'[Adjustments]) * CALCULATE([Reimbursement], ALLSELECTED('Table'[Location])), CALCULATE( SUM('Table'[Adjustments]), ALLSELECTED('Table'[Location])))And with that, I'm able to create the following matrix:
If you have any questions, feel free to follow up here.
Reposting my edit so you get the notification
It should be summed at the location level and then rolled up to the vendor level, just like you said. I think the measure you wrote should work. I'll test it out and let you know what happens!
So I think that the measure works, but its not exactly what I'm looking for, sorry for not explaining better:
Basically, we have four locations, let's say they all have an allowance of $2000, so $8000 total. Locations 3 and 4 rack up 9000 in adjustments total. We can borrow against Locations 1 and 2's allowances to offset the total reimbursement owed. Here is an excel screenshot that kind of shows what I mean.
Ah, so since other Locations can borrow against each other under the same vendor, the expression becomes simpler:
Reimbursement = IF(SUM('Table'[adjustments])+SUM('Table[allowance]) > 0, 0, SUM('Table'[adjustments])+SUM('Table'[allowance]) ) )
This has the issue of letting vendors "borrow" against each other in the same way for the grand total, but we can fix that by combining it with the original solution and grouping at the Vendor level. I've also added in a *-1 here because your examples have reimbursement as a positive value.
Reimbursement = SUMX( VALUES('Table'[Vendor]), CALCULATE(IF(SUM('Table'[adjustments])+SUM('Table'[allowance]) > 0, 0, (SUM('Table'[adjustments])+SUM('Table'[allowance]))*-1 )) )
Now we start getting really tricky in order to get the weighted reimbursement in the same measure that calculates the correct amount at the Vendor level or higher. Luckily, we already have a working [Reimbursement] measure, so we're going to re-use that. I use DIVIDE instead of the normal division symbol here because it seems likely that the sum of adjustments can be zero, which causes errors. DIVIDE handles division by zero errors much more gracefully than the / symbol does.
Weighted Reimbursement = DIVIDE(SUM('Table'[Adjustments]) * CALCULATE([Reimbursement], ALLSELECTED('Table'[Location])), CALCULATE( SUM('Table'[Adjustments]), ALLSELECTED('Table'[Location])))And with that, I'm able to create the following matrix:
If you have any questions, feel free to follow up here.
Excellent work, as usual, Cmcmahan !
Thank you for taking the time to help me with this, I just couldn't quite wrap my head around the equation.