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

[Applicable88]

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

[VahidDM · Accepted solution]

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:

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/

[Ashish_Mathur · Accepted solution]

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.

[VahidDM]

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/

[Applicable88]

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

[VahidDM · Accepted solution]

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:

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/