Creating a new table in Power BI using aggregated data from other tables

[abehrmann]

I have a data set looking at workorders my company completes,  there are workorder #s and users assiged to them. 

i am looking to create a new table that agregates varous metrics from the data set.  currently in my new table i have listed the users and attepting to average the time to complete work orders.  but usin the AVG  function, but it is returning the average for the entire data set not the specific users aversage.  The goal for me is to get some stats on each user to eventually rank them based on each column and then rank them based on an agg of their ranks in each category. 

Any help is appreciated

Thanks, 

[Anonymous]

@abehrmann you need to use allexcept to avg it at a users level

CALCULATE(AVG(table1[worktime], allexcept(table1, table1[user]))

https://msdn.microsoft.com/en-us/query-bi/dax/allexcept-function-dax

once you have all the metrics, if you want to create an agg table with them you can use calculated tables. I think you can create using distinct and values function. chck details in the next link(https://docs.microsoft.com/en-us/power-bi/desktop-calculated-tables).