Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
abehrmann
Helper II
Helper II

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

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, 

1 REPLY 1
Anonymous
Not applicable

@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).

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.