none
How can I calculate the TRIMMEAN formula in powerpivot Excel 2010 ? RRS feed

  • 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
    Thursday, March 9, 2017 6:55 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 file        

    There 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 index-column during import.

                  

    Imke Feldmann TheBIccountant.com



    Saturday, March 18, 2017 12:14 PM
    Moderator

All replies

  • How can I calculate the TRIMMEAN formula in powerpivot Excel 2010?
    Thursday, March 2, 2017 7:17 PM
  • How can I calculate the TRIMMEAN formula in powerpivot Excel 2010?
    Thursday, March 2, 2017 7:22 PM
  • 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.


    Friday, March 3, 2017 6:21 AM
    Moderator
  • 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.


    Friday, March 10, 2017 7:25 AM
    Moderator
  • Thanks for the answer, but the result is not correct

    

    Monday, March 13, 2017 4:07 PM
  • 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.


    Tuesday, March 14, 2017 6:57 AM
    Moderator
  • My real table has many codes in number format (CODE OUVRAGE).

    Table Powerpivot

    The graph (calcul column (PRIX UNITE REEL))

    Thanks

    Wednesday, March 15, 2017 8:34 PM
  • 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

    Friday, March 17, 2017 10:25 AM
    Moderator
  • 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!!!!!


    Friday, March 17, 2017 1:19 PM
  • 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 file        

    There 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 index-column during import.

                  

    Imke Feldmann TheBIccountant.com



    Saturday, March 18, 2017 12:14 PM
    Moderator
  • Hi Carlos,

    could you make this solution work for you?


    Imke Feldmann
    MVP Data Platform
    TheBIccountant.com

    Saturday, April 1, 2017 4:48 AM
    Moderator