Forum Discussion
Calculate category average based on selected record
Hello,
I'm trying to find a way to utilize DAX to help us compare numerous project metrics against other projects with the same market sector categorization.
Example:
Projects will have many records of various metrics. One example might be Lead Times for any number of items. Project A is classified as "Residential". Project A's average lead time is 12.5 days. If Project A is selected, I have a card that displays average lead time for Project A, and would like to feature another card that shows the average lead time across all "Residential" projects. Intended outcome would be to allow a project to be benchmarked against similar historical projects on these metrics.
I've tried to achieve this using existing forum posts but seem to be leaving in the project filter in my calculation, as I get the same average in both cases (which is incorrect).
I currently have a project table that features a Project ID (Primary Key) as well as that projects classifier. Several other tables each with the actual data I am looking to calculate connect to the project table with the Project ID. I have a sample dataset I can provide but can't find a way to attach to this post.
My mind went to using a measure to accomplish this, but would it make more sense to create a calculated or custom column? Any pointers would be appreciated!
Hi,
In the Table visual, you will see 10.43 in the Grand total row
I was able to solve this and have it display on cards as intended with the following measures:
AvgLeadTimeSelectedProject =AVERAGE('Metric Table'[Lead Time])AvgLeadTimeSameMarketSector =CALCULATE(AVERAGE('Metric Table'[Lead Time]),ALL('Project Table'),'Project Table'[Market Sector] = SELECTEDVALUE('Project Table'[Market Sector]))
10 Replies
I was able to solve this and have it display on cards as intended with the following measures:
AvgLeadTimeSelectedProject =AVERAGE('Metric Table'[Lead Time])AvgLeadTimeSameMarketSector =CALCULATE(AVERAGE('Metric Table'[Lead Time]),ALL('Project Table'),'Project Table'[Market Sector] = SELECTEDVALUE('Project Table'[Market Sector]))Hi, lukaszj97
Glad to help you, you can use Google Drive to share your pbix files (please set up no need to log in to a Google account to access).Best Regards,
Yang
Community Support TeamHi, let me know if this link works for you Forum Sample Power BI.pbix
Hi,
I have solved a similar problem in the attached file.
Hope this helps.
Hi Ashish,
I think your solution is similar but I am lookin to do a calculation of the values associated with all matching categories. I wasn't able to attach a .PBIX but here is a screenshot of the sample data and use case. Note the Site Average Cards are averaging only the selected site, in this case B.
I am not sure of how much i can help but i can try. Share the download link of the PBI file.
