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

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

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

    • Ashish_Mathur's avatar
      Ashish_Mathur
      Icon for Super User rankSuper 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.