Home

Calculate median in excel pivot table

%3CLINGO-SUB%20id%3D%22lingo-sub-1038633%22%20slang%3D%22en-US%22%3ECalculate%20median%20in%20excel%20pivot%20table%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1038633%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20all%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20trying%20to%20insert%20a%20median%20column%20next%20to%20grand%20total%20in%20the%20below%20pivot%20table.%20I%20don't%20want%20the%20median%20count%20of%20specialist%20role%2C%20I%20want%20the%20median%20Day%20Rate%20(from%20extracted%20price%20or%20price%20rounded%20to%20100%20field).%20The%20average%20isn't%20working%20effectively%20(as%20shown%20by%20right%20hand%20table)%20so%20median%20more%20likely%20to%20be%20effective.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHow%20might%20I%20do%20this%3F%20Is%20this%20possible%20with%20the%20calculated%20field%20window%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMany%20thanks!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F159355iEF1C0D1069275477%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22clipboard_image_0.png%22%20title%3D%22clipboard_image_0.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1038633%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1038863%22%20slang%3D%22en-US%22%3ERe%3A%20Calculate%20median%20in%20excel%20pivot%20table%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1038863%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F471915%22%20target%3D%22_blank%22%3E%40ESGSanders%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EBetter%20if%20creating%20the%20PivotTable%20you%20check%20the%20option%20to%20add%20data%20to%20data%20model%20and%20add%20measure%20to%20PivotTable%20using%20DAX%20MEDIAN()%20function%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fdax%2Fmedian-function-dax%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fdax%2Fmedian-function-dax%3C%2FA%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAdding%20the%20measure%20right%20click%20on%20table%20name%20in%20right%20PivotTable%20pane%20and%20add%20formula%20into%20the%20form.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
ESGSanders
Occasional Visitor

Hi all,

 

I am trying to insert a median column next to grand total in the below pivot table. I don't want the median count of specialist role, I want the median Day Rate (from extracted price or price rounded to 100 field). The average isn't working effectively (as shown by right hand table) so median more likely to be effective.

 

How might I do this? Is this possible with the calculated field window?

 

Many thanks!

 

clipboard_image_0.png

1 Reply

@ESGSanders 

Better if creating the PivotTable you check the option to add data to data model and add measure to PivotTable using DAX MEDIAN() function https://docs.microsoft.com/en-us/dax/median-function-dax

 

Adding the measure right click on table name in right PivotTable pane and add formula into the form.

Related Conversations
PivotTable : Unexpected behavior with 2 tables
CastyChichi in Excel on
2 Replies
Problems using Pivot tables in Excel 16.25 on Mac
jeromepineau in Excel on
0 Replies
Set Auto Refresh to Pivot Table as Source Data Changes
ratishkp in Excel on
2 Replies
Counting Days
Tim Hunter in SQL Server on
2 Replies