Weighted Reimbursement Formula

[Anonymous]

I'm trying to get a weighted reimbursement formula from an Excel file to work. 

  

Reimbursement=(Location Reimbursement/Sum of all locations reimbursements)*Total Vendor Reimbursement

Here is how my Power Bi data is organized:

I appreciate the help in advance!

[Cmcmahan]

Sure.  How are you specifying the current location?  Is it through a slicer, or are you listing all the locations in a table with this measure? How is the reimbursement amount calculated in the first place?  In the table you shared, there's only date, vendor, location, item, and tranqty. 

I'll write a weighted reimbursement measure, but it assumes you have a regular working [Reimbursement] measure that sums up the total reimbursement of all currently selected rows.

Weighted Reimbursement= [Reimbursement] / CALCULATE([Reimbursement], ALL(Table[Locations]) * CALCULATE([Reimbursement], ALLEXCEPT(Table, Table[Vendor]))

[Anonymous]

Hey @Cmcmahan ,

Thanks for taking the time to help me out! Reimbursement comes from two other columns: Adjustments (amount owed, usually negative)+Allowance( amount we are allowed to lose)

if(adjustments+allowance>0,0,adjustments+allowance)

-200+100=100 reimbursement

Here are some screenshots of how my matrix is setup:

1. Top view is Overall Reimbursement by Vendor

2. Expand all one level down in the hierarchy view has vendor with each location broken out.

Hopefully, this helps my setup make a bit more sense. In the meantime, I will try to get the weighted formula you wrote to work.

[Cmcmahan]

Sure.  Does it make sense (with your data) at the vendor level to sum up all Adjustments and Allowances, and use that to calculate reimbursement? Or should it be calculated at a location level, and then those results are summed together for the Vendor level?

So I would write a reimbursement calculation like this if they should be calculated at the Location level and summed up for any higher levels:

Reimbursement = SUMX( 'Table', IF('Table'[adjustments]+'Table[allowance] > 0, 0, 'Table'[adjustments]+'Table'[allowance]) )

And that should be usable with the previous measure I provided. 

[Anonymous]

Reposting my edit so you get the notification

@Cmcmahan 

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.

[Cmcmahan · Accepted solution]

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.

[Anonymous]

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.