I have two tables, Resource Postings and Resource Bids.
Resource Postings -> Resource Bids is a one to many relationship. Resource Postings contains a list of posted job roles (i.e., Project Manager, DBA, Program Manager) and their geographic locations. Resource Bids contains a list of supplier bids for hourly rates to fill the roles in the Resource Postings table.
I want a dynamic DAX measure that calculates the average bid for the role type and location (I.e., average hourly rate bid for Project Mangers in NYC if that is the current row context). This will be used in Excel PowerPivot tables to actually display the value, so shortcuts with visualizations or hard coding categories in the formula won't work.
Here is what I have so far:
Average Bid for Role & Location:=SUMX('Resource Postings',CALCULATE(AVERAGEX('Resource Bids','Resource Bids'[Hourly Rate]), FILTER(ALL('Resource Bids'),COUNTROWS(FILTER('Resource Bids','Resource Bids'[Hourly Rate]<>BLANK()))), FILTER(ALL('Resource Postings'), COUNTROWS(FILTER('Resource Postings', EARLIER('Resource Postings'[Location]) = 'Resource Postings'[Location] && EARLIER('Resource Postings'[Role]) = 'Resource Postings'[Role])))))
I feel that I am 90% of the way there, the problem is aggregation. The subtotals include averages for projects that a supplier has not bid on.
If they bid all four its correct:
| Supplier | Resource Posting | Hourly Rate | Average Bid for Role & Location | Difference from Average (R & L) | % Over (Under) Average (R & L) |
| Company A | Posting A | 25 | 37.92 | -12.92 | -34.07% |
| | Posting B | 25 | 37.92 | -12.92 | -34.07% |
| | Posting C | 25 | 37.92 | -12.92 | -34.07% |
| | Posting D | 45 | 37.92 | 7.08 | 18.67% |
| Company A Total | | 120 | 151.68 | -31.68 | -20.89% |
If they've only bid two so far it still grand totals as if they've bid four, yet the Difference from Average calculation is fine.
| Supplier | Resource Posting | Hourly Rate | Average Bid for Role & Location | Difference from Average (R & L) | % Over (Under) Average (R & L) |
| Company B | Posting A | 26 | 37.92 | -11.92 | -31.43% |
| | Posting B | 24 | 37.92 | -13.92 | -36.71% |
| Company B Total | | 50 | 151.68 | -25.84 | -17.04% |
I will be using this measure to quantify % under / over benchmark for each bid based on location and role type. Then, I'll use RANKX to rank each supplier on their comparative weighted average performance relative to the location and role benchmarks.
