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

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
Applicable88
Impactful Individual
Impactful Individual

How to get the total average of summarize collumns into the rows of its own category?

Hello, 

I tired do a calculated table where in every row is the total average of its own categeroy for orders which are status = "Done". 


The average column is what I want to calculate. The respective row always shows the total average value of its category and Status needs to be "Done":

 

OrderID  Category Status Seconds Average
1234 Fetch Done 100 168,333333
1235 Fetch  Done 150 168,333333
1236 Fetch Done 200 168,333333
1237 Fetch  Done 200 168,333333
1238 Fetch Done 300 168,333333
1239 Fetch  Done 60 168,333333
1240 Move Done 100 83,3333333
1241 Move Done 50 83,3333333
1242 Move Done 100 83,3333333
1243 Move Fail 50 0

I tried several approaches with summarize , addcolumns and Filter (All... , but I always get the seconds of its own row.

 

Thank you very much in advance. 

Best. 

 

 

 

 

 

 

 

 

OrderID  Category Seconds Average
1234 Fetch 100 168,333333
1235 Fetch  150 168,333333
1236 Fetch 200 168,333333
1237 Fetch  200 168,333333
1238 Fetch 300 168,333333
1239 Fetch  60 168,333333
1240 Move 100 75
1241 Move 50 75
1242 Move 100 75
1243 Move 50 75
2 ACCEPTED SOLUTIONS

Hi @Applicable88 

 

If I hot your point corrctly, try this code to add a new column: 

Average  = 
CALCULATE( AVERAGE( 'Table'[Seconds] ), ALLEXCEPT( 'Table', 'Table'[Category] ))

 

output:

VahidDM_0-1642637247353.png

 

 

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
LinkedIn: 
www.linkedin.com/in/vahid-dm/

 

 

View solution in original post

Ashish_Mathur
Super User
Super User

Hi,

This calculated column formula works

Average = if(Data[Status]="Done",CALCULATE(AVERAGE(Data[Seconds]),FILTER(Data,Data[Category]=EARLIER(Data[Category])&&Data[Status]="Done")),0)

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

4 REPLIES 4
Ashish_Mathur
Super User
Super User

Hi,

This calculated column formula works

Average = if(Data[Status]="Done",CALCULATE(AVERAGE(Data[Seconds]),FILTER(Data,Data[Category]=EARLIER(Data[Category])&&Data[Status]="Done")),0)

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
VahidDM
Super User
Super User

Hi @Applicable88 

 

Which one of those tables you shares is the result and which one is the sample data? Can you add more details?

 


Appreciate your Kudos!!
LinkedIn: 
www.linkedin.com/in/vahid-dm/

 

 

@VahidDM , thanks for the quick reply. First three columns are sample data. Column "Average" is my preferred outcome. 

Hi @Applicable88 

 

If I hot your point corrctly, try this code to add a new column: 

Average  = 
CALCULATE( AVERAGE( 'Table'[Seconds] ), ALLEXCEPT( 'Table', 'Table'[Category] ))

 

output:

VahidDM_0-1642637247353.png

 

 

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
LinkedIn: 
www.linkedin.com/in/vahid-dm/

 

 

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

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

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.