Calculate category average based on selected record

[lukaszj97]

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!

[Ashish_Mathur · Accepted solution]

Hi,

In the Table visual, you will see 10.43 in the Grand total row

[lukaszj97 · Accepted solution]

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]))

[Ashish_Mathur]

Hi,

I have solved a similar problem in the attached file.

Hope this helps.

[lukaszj97]

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.

[Ashish_Mathur]

I am not sure of how much i can help but i can try.  Share the download link of the PBI file.

[lukaszj97]

Let me know if this link works for you Forum Sample Power BI.pbix

[Ashish_Mathur]

Hi,

In the attached PBI file, you will see the table working correctly with 73 being shown in the Grand total cell.  What number do you want to see there?

[lukaszj97]

Hi Ashish, The desired outcome would be the average lead time for all items on sites A and B. Therefore, the final answer in this sample should be 10.4 for average lead time when either A or B are selected.

I'm thinking I will have to utilize either allexcept() or Allselected() to get around using the project filter. Will report back if I have any successful attempts.

[Ashish_Mathur · Accepted solution]

Hi,

In the Table visual, you will see 10.43 in the Grand total row

[Anonymous]

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 Team

[lukaszj97]

Hi, let me know if this link works for you Forum Sample Power BI.pbix