Search code examples
excelpivot-tablepowerpivot

Excel - Add function to those defined in Pivot Table

Good afternoon!

I've made a Pivot table from the data in the PowerPivot.

I would like to have in my pivot table the AverageIFS function instead of the Average one, because I want to calculate the average not including the registers that have 0. In the picture, the column that I use to calculate the average is Duree_Deplacem (in the picture).

I have found some information about what I need in https://support.office.com/en-us/article/change-the-summary-function-or-custom-calculation-for-a-field-in-a-pivottable-report-ea8945fb-9969-4bac-a16c-4f67b0f7b239, but it only uses the defined functions and doesn't give the option to use other functions. That makes me think it may be impossible. (?).

Solution

  • If you are using PowerPivot to make pivot table reports, it's a big mistake trying to use calculated fields in a pivot table. It's like using a horse to pull a Ferrary.

    PowerPivot has its own language called DAX, with which you can write dynamic formulas that are much more powerful and flexible than the calculated fields. But you will need to invest some time to educate yourself - you must understand what you are doing. Here is an example of a starter tutorial

    Once you understand what a Measure is, your problem can be solved as follows:

    Average Without Zeros = CALCULATE( AVERAGE(Table[Field]), Table[Field] > 0)

    where Table is the name of your table, and Field is the name of the field you are averaging.

    If you drop this measure into a pivot table, it will calculate averages correctly, in any pivot table layout.

    Once you see how it works, your life will never be the same :)

    If you decide to learn DAX and PowerPivot more seriously, I recommend this book to start with:

    Power Pivot and Power BI: The Excel User's Guide to DAX