Answered by:
How can I calculate the TRIMMEAN formula in powerpivot Excel 2010 ?
Question

How can I calculate the TRIMMEAN formula in powerpivot Excel 2010?
I need create the TRIMMEAN 40%. I have only average (moyenne).
Thanks
 Edited by Carlos 2017 Thursday, March 9, 2017 6:56 PM
Answers

A dynamic version of it can be achieved by tweaking Angelia's first version a bit:
Instead of creating Rank in a column, create a measure like this:
Rank := RANKX (ALLEXCEPT ( Table4, Table4[codigo] ), [SumColumn],, 0, SKIP)
with
SumColumn := SUM ( Table4[column] )
in the "number"measure, some important changes are made:
number := CALCULATE ( COUNT ( Table4[column] ), ALLEXCEPT ( Table4, Table4[codigo] ) )
this will count all items per codigo
The final measure will look like this then
TM40% :=
CALCULATE (
AVERAGE ( Table4[column] ),
FILTER (
ALLEXCEPT ( Table4, Table4[codigo] ),
AND (
[Rank]
> INT ( [number] * 0.4 * 0.5 ),
[Rank]
<= (
[number]
 INT ( [number] * 0.4 * 0.5 )
)
)
)
)
Link to fileThere are some flaws with regards to duplicates/ties. If this is an issue, make sure you have a column with unique keys in your table. Otherwise create an indexcolumn during import.
Imke Feldmann TheBIccountant.com
 Edited by Imke FeldmannMVP, Editor Saturday, March 18, 2017 1:42 PM
 Proposed as answer by Angelia ZhangMicrosoft contingent staff, Moderator Monday, March 20, 2017 1:52 AM
 Marked as answer by Michael AmadiModerator Sunday, April 2, 2017 5:20 PM
All replies

How can I calculate the TRIMMEAN formula in powerpivot Excel 2010?
 Merged by Imke FeldmannMVP, Editor Saturday, April 1, 2017 5:12 AM Same question from OP

How can I calculate the TRIMMEAN formula in powerpivot Excel 2010?
 Merged by Michael AmadiModerator Thursday, March 2, 2017 11:44 PM Duplicate thread

Hi Carlos,
I reproduce your scenario in Power Pivot excel 2010 and get expected result.
This is my sample data you can copy it here. Create a linked table, add the sample table into pivot model.
First, you should add a rank column used to rank all the value.
=RANKX(Table4,Table4[column])
Then create measure, I list the percent 20% and percent 40% as example, please review the following screenshot.number:=CALCULATE(COUNT(Table4[column]),ALL(Table4)) 20%:=CALCULATE(AVERAGE(Table4[column]),FILTER(Table4,AND(Table4[Rank]>INT(Table4[number]*0.2*0.5),Table4[Rank]<=(Table4[number]INT(Table4[number]*0.2*0.5))))) 40%:=CALCULATE(AVERAGE(Table4[column]),FILTER(Table4,AND(Table4[Rank]>INT(Table4[number]*0.4*0.5),Table4[Rank]<=(Table4[number]INT(Table4[number]*0.4*0.5)))))
Compare the result in Power Pivot with excel, it's correct.
Best Regards,
Angelia
MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com. Edited by Angelia ZhangMicrosoft contingent staff, Moderator Friday, March 3, 2017 6:29 AM
 Proposed as answer by Imke FeldmannMVP, Editor Saturday, March 4, 2017 7:47 AM

Hi Carlos 2017,
According to your screenshot, you create a calculated column, right? TRIMMEAN in Excel is to calculate one result(trimmed mean) based on a supplied set of values. Measures are used in some of the most common data analysis based on function. So you should create a measure rather than calculated column like posted in this thread.
Best Regards,
Angelia
MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com. Edited by Angelia ZhangMicrosoft contingent staff, Moderator Friday, March 10, 2017 7:25 AM


Hi Carlos 2017,
The result is not correct, because your sample data is different from mine. My solution is to calculate the trimmed mean of all values in column. While in your sample data, when you select "a", it will return the trimmed mean of all "a" values? Correspondingly, it will return will return the trimmed mean of all "b" values when you click b?
If it is, you should create two seperate measures.
Rank = RANKX(FILTER(Table4,Table4[codigo]=EARLIER(Table4[codigo])),Table4[column],,ASC,Dense) number_a:=CALCULATE(COUNT(Table4[column]),Filter(ALL(Table4),Table4[codigo]="a")) 40%_a:=CALCULATE(AVERAGE(Table4[column]),FILTER(Table4,AND(Table4[Rank]>INT(Table4[number_a]*0.4*0.5),Table4[Rank]<=(Table4[number_a]INT(Table4[number_a]*0.4*0.5))))) number_b:=CALCULATE(COUNT(Table4[column]),Filter(ALL(Table4),Table4[codigo]="b")) 40%_b:=CALCULATE(AVERAGE(Table4[column]),FILTER(Table4,AND(Table4[Rank]>INT(Table4[number_b]*0.4*0.5),Table4[Rank]<=(Table4[number_b]INT(Table4[number_b]*0.4*0.5)))))
When you select a, you should return 40%_a, and return 40%_b when you click b. Just lick calculate trimmed mean in Excel, it will return different result when you select different scope values.
Best Regards,
Angelia
MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com. Edited by Angelia ZhangMicrosoft contingent staff, Moderator Tuesday, March 14, 2017 6:59 AM


Hi Carlos,
it is not clear to me what your last post meant: Is your problem solved? Then please mark the post that helped it as answer.
If it still doesn't work, please specify.
Imke Feldmann TheBIccountant.com

Hi,
My problem is not solved.
number_a:=CALCULATE(COUNT(Table4[column]),Filter(ALL(Table4),Table4[codigo]="a"))
I have many codes in number format (CODE OUVRAGE). I need calculate TRIMMEAN for each code, there are 900 codes approximately. My really table date is the picture.
Thanks!!!!!

A dynamic version of it can be achieved by tweaking Angelia's first version a bit:
Instead of creating Rank in a column, create a measure like this:
Rank := RANKX (ALLEXCEPT ( Table4, Table4[codigo] ), [SumColumn],, 0, SKIP)
with
SumColumn := SUM ( Table4[column] )
in the "number"measure, some important changes are made:
number := CALCULATE ( COUNT ( Table4[column] ), ALLEXCEPT ( Table4, Table4[codigo] ) )
this will count all items per codigo
The final measure will look like this then
TM40% :=
CALCULATE (
AVERAGE ( Table4[column] ),
FILTER (
ALLEXCEPT ( Table4, Table4[codigo] ),
AND (
[Rank]
> INT ( [number] * 0.4 * 0.5 ),
[Rank]
<= (
[number]
 INT ( [number] * 0.4 * 0.5 )
)
)
)
)
Link to fileThere are some flaws with regards to duplicates/ties. If this is an issue, make sure you have a column with unique keys in your table. Otherwise create an indexcolumn during import.
Imke Feldmann TheBIccountant.com
 Edited by Imke FeldmannMVP, Editor Saturday, March 18, 2017 1:42 PM
 Proposed as answer by Angelia ZhangMicrosoft contingent staff, Moderator Monday, March 20, 2017 1:52 AM
 Marked as answer by Michael AmadiModerator Sunday, April 2, 2017 5:20 PM

Hi Carlos,
could you make this solution work for you?
Imke Feldmann
MVP Data Platform
TheBIccountant.com