SOLVED

Analysing Multiple Cells in a Single Field in Pivot Table

%3CLINGO-SUB%20id%3D%22lingo-sub-2081902%22%20slang%3D%22en-US%22%3EAnalysing%20Multiple%20Cells%20in%20a%20Single%20Field%20in%20Pivot%20Table%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2081902%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20all.%20This%20is%20my%20first%20post%20here.%20I%20am%20sorry%20if%20I%20have%20missed%20anything.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMy%20problem%20is%20that%20I%20have%20a%20few%20hundred%20complaint%20cases%20on%20a%20fleet%20of%20buses%20regarding%20their%20maintenance.%20My%20task%20is%20to%20analyse%20the%20common%20maintenance%20problems%20and%20how%20did%20the%20parts%20malfunctioned.%20Attached%20you%20may%20find%20a%20mini-template%20similar%20to%20my%20original%20excel%20file%2C%20which%20is%20in%20Chinese.%20A%20bus%20can%20have%20single%20or%20multiple%20problems%20found%20in%20a%20single%20defect.%20For%20example%2C%20if%20the%20air-conditioning%20is%20insufficient%2C%20it%20can%20be%20attributed%20to%20broken%20air-conditioning%20belt%20in%20a%20scenario%20and%20broken%20air-conditioning%20belt%20and%20dirty%20condenser%20in%20another%20scenario.%20I%20would%20like%20to%20include%20all%20the%20problems%20found%20in%20the%20pivot%20table%20analysis.%20However%2C%20in%20the%20attached%20excel%20file%2C%20I%20can%20only%20include%20the%20first%20problem%20shown%20in%20column%20C%20in%20the%20pivot%20table%20analysis.%20The%20remaining%20problems%20shown%20in%20column%20D%20and%20E%20are%20all%20left%20out.%20Is%20there%20a%20way%20that%20I%20can%20include%20all%20the%20problems%20found%20bringing%20a%20defect%20of%20a%20bus%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20a%20lot%20in%20advance!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2081902%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EBI%20%26amp%3B%20Data%20Analysis%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2082222%22%20slang%3D%22en-US%22%3ERe%3A%20Analysing%20Multiple%20Cells%20in%20a%20Single%20Field%20in%20Pivot%20Table%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2082222%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F937341%22%20target%3D%22_blank%22%3E%40penguin_wai%3C%2FA%3E%26nbsp%3B%2C%20you%20may%20add%20data%20in%20columns%20D%20and%20E%20to%20data%20source%20with%20some%20names%20and%20show%20them%20in%20hierarchy.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2082255%22%20slang%3D%22en-US%22%3ERe%3A%20Analysing%20Multiple%20Cells%20in%20a%20Single%20Field%20in%20Pivot%20Table%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2082255%22%20slang%3D%22en-US%22%3E%3CP%3EHi%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20for%20your%20reply.%20I%20have%20tried%20your%20method.%20As%20shown%20in%20the%20attached%2C%20the%20problems%20in%20columns%20D%20and%20E%20are%20displayed%20as%20the%20sub-problems%20of%20those%20in%20column%20C.%20Is%20there%20a%20way%20to%20display%20the%20problems%20in%20each%20cell%20indepedently%20and%20counted%20in%20the%20Grand%20Total%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

Hi all. This is my first post here. I am sorry if I have missed anything.

 

My problem is that I have a few hundred complaint cases on a fleet of buses regarding their maintenance. My task is to analyse the common maintenance problems and how did the parts malfunctioned. Attached you may find a mini-template similar to my original excel file, which is in Chinese. A bus can have single or multiple problems found in a single defect. For example, if the air-conditioning is insufficient, it can be attributed to broken air-conditioning belt in a scenario and broken air-conditioning belt and dirty condenser in another scenario. I would like to include all the problems found in the pivot table analysis. However, in the attached excel file, I can only include the first problem shown in column C in the pivot table analysis. The remaining problems in columns D and E shown in a red circle are all left out. Is there a way that I can include all the problems found bringing a defect of a bus?

 

Thanks a lot in advance!

5 Replies

@penguin_wai , you may add data in columns D and E to data source with some names and show them in hierarchy. 

Hi @Sergei Baklan

 

Thanks for your reply. I have tried your method. As shown in the attached, the problems in columns D and E are displayed as the sub-problems of those in column C. Is there a way to display the problems in each cell indepedently and counted in the Grand Total?

Best Response confirmed by penguin_wai (New Contributor)
Solution

@penguin_wai 

I'd use Power Query to transform the data, result will be as

image.png

 

@Sergei Baklan I have applied power query and successfully solved the problem. Thanks Sergei!