Weighted Average Based on Variable Benchmarks

[Anonymous]

Hi all,

Thanks in advance for the help! Basically, I need to compare different values to benchmark values and loop the difference into a weighted average by service. The trick is that I need to be able to change the benchmark values based on my slicer selections. 

In more detail, I have data at the code-level that is grouped by service. Each code has an associated reimbursement that I need to benchmark (based on values in a different file that link by the code). I then execute the equation (1 - (Benchmark Value / Reimbursement)) in order to get the "discount" to benchmark. (e.g. Code 69436 would need to be discounted 20% to match the benchmark).

I then need to get a weighted average to see the overall discount by service. My solution to this is to use a helper column that multiplies the # of accounts by the discount. I then add up the helper column total by service and divide by the # of accounts by service to get the weighted average. 

I can get the discounts by service easily enough, but I haven't been able to filter the benchmark value and have it work into the equation. 

Thanks in advance for the help! 

[v-chuncz-msft · Accepted solution]

@Anonymous 

You may try SELECTEDVALUE to add a measure.