Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
AdamM
Frequent Visitor

DAX Measure to calculate average by category using current row as criteria

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:

 

SupplierResource PostingHourly RateAverage Bid for Role & LocationDifference from Average (R & L)% Over (Under) Average (R & L)
Company APosting A2537.92-12.92-34.07%
 Posting B2537.92-12.92-34.07%
 Posting C2537.92-12.92-34.07%
 Posting D4537.927.0818.67%
Company A Total 120151.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.

 

SupplierResource PostingHourly RateAverage Bid for Role & LocationDifference from Average (R & L)% Over (Under) Average (R & L)
Company BPosting A2637.92-11.92-31.43%
 Posting B2437.92-13.92-36.71%
Company B Total 50151.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. 

2 REPLIES 2
Ashish_Mathur
Super User
Super User

Hi,

 

 

Share the link from where i can download your file.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Greg_Deckler
Super User
Super User

OK, before tackling this problem, I believe more information is needed. For example, I do not see Location anywhere in your data other than your formula. How does Location work? Same for Role, where is that and how does that work? Finally, it would be extremely helpful to understand your desired results for checking.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.