SOLVED

How to create pivot table with median values?

%3CLINGO-SUB%20id%3D%22lingo-sub-3196206%22%20slang%3D%22en-US%22%3EHow%20to%20create%20pivot%20table%20with%20median%20values%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3196206%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20seeking%20to%20make%20a%20pivot%20table%20that%20shows%20the%20different%20median%20values%20for%20different%20countries%20from%20a%20list%20of%20table.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECould%20someone%20show%20me%20how%20to%20create%20pivot%20table%20with%26nbsp%3B%3CU%3E%3CSTRONG%3Emedian%3C%2FSTRONG%3E%3C%2FU%3E%3CU%3E%20values%3F%3C%2FU%3E%3CSTRONG%3E%26nbsp%3B%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECurrently%2C%20my%20pivot%20table%20only%20shows%26nbsp%3B%3CU%3E%3CSTRONG%3Eaverage%3C%2FSTRONG%3E%3C%2FU%3E%3CU%3E%26nbsp%3B%3C%2FU%3Evalues.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20pivot%20table%20is%20not%20possible%2C%20it%26nbsp%3B%20would%20be%20great%20if%20you%20could%20show%20me%20how%20to%20use%20XLOOKUP%20function%20or%20another%20function%20and%20kindly%20explain%20the%20rationale.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%2C%3C%2FP%3E%3CP%3EAmy%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3196206%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3196908%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20create%20pivot%20table%20with%20median%20values%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3196908%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1260738%22%20target%3D%22_blank%22%3E%40AmyYang%3C%2FA%3E%26nbsp%3BSee%20attached.%20Have%20used%20UNIQUE%20and%20FILTER%20to%20achieve%20what%20you%20asked%20for.%20See%20the%20green%20shaded%20area%20in%20the%20attached%20file.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3196994%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20create%20pivot%20table%20with%20median%20values%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3196994%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1260738%22%20target%3D%22_blank%22%3E%40AmyYang%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESolution%20with%20formula%2C%20Power%20Query%20and%20DAX%20Measure.%3C%2FP%3E%3CP%3ESee%20attached%20file.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Contributor

Hi, 

 

I am seeking to make a pivot table that shows the different median values for different countries from a list of table.

 

Could someone show me how to create pivot table with median values? 

 

Currently, my pivot table only shows average values. 

 

If pivot table is not possible, it  would be great if you could show me how to use XLOOKUP function or another function and kindly explain the rationale.

 

Thank you,

Amy 

4 Replies
best response confirmed by AmyYang (Contributor)
Solution

@AmyYang See attached. Have used UNIQUE and FILTER to achieve what you asked for. See the green shaded area in the attached file. 

@AmyYang 

Solution with formula, Power Query and DAX Measure.

See attached file.

 

@Riny_van_Eekelen Hi Riny, that is amazing idea, thanks so much! 

Hi Detlef, wow, a pivot table was really created with median values!! Thank you! I will search on how to use Power Query + DAX measure to recreate this for my other files too. Thank you so much!