Forum Discussion

lukaszj97's avatar
lukaszj97
Frequent Visitor
2 years ago
Solved

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...
Need Help
  • lukaszj97's avatar
    lukaszj97
    2 years ago

    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's avatar
Ashish_Mathur
Super User
2 years ago

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's avatar
    lukaszj97
    Frequent Visitor
    2 years ago

    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.