Power Pivot - Error in Summary calculations with Cross tab for ABC, XYZ Analysis

%3CLINGO-SUB%20id%3D%22lingo-sub-1747510%22%20slang%3D%22en-US%22%3EPower%20Pivot%20-%20Error%20in%20Summary%20calculations%20with%20Cross%20tab%20for%20ABC%2C%20XYZ%20Analysis%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1747510%22%20slang%3D%22en-US%22%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Capture.PNG%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F224315i0587FA02F69B26D0%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22Capture.PNG%22%20alt%3D%22Capture.PNG%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%3CBR%20%2F%3EI%20have%20a%20crosstab%20excel%20file%20with%20columns%20of%20Product%2C%20Product%20description%2C%20Sale%20in%20Kg%20for%2012%20months%20in%2012%20columns%2C%20corresponding%20Sale%20in%20Value%20(Euros)%20for%2012%20months%20in%20the%20next%2012%20columns.%20I%20made%20this%20base%20sheet%20as%20a%20Table%20and%20added%20to%20Power%20pivot.%20Now%20I%20need%20to%20do%20the%20ABC%2C%20XYZ%20analysis%20based%20on%20the%20qty%20and%20Value.%20Taking%20the%20last%2012%20months%2C%20I%20am%20trying%20to%20calculate%20the%20yearly%20total%20sum%20of%20the%20quantity%20in%20Kg%20and%20the%20yearly%20total%20sum%20of%20value%20in%20Euros.%20In%20the%20add%20column%20in%20power%20pivot%2C%20I%20used%20a%20Sum%20function%2C%20selected%20the%2012%20months%20Sales%20Kg%20values%20from%20dropdown%20and%20another%20add%20column%2C%20again%20used%20the%20Sum%20function%2C%20selected%2012%20months%20Sale%20in%20Value%20(Euro).%20But%20the%20values%20are%20not%20totaling%20up%20and%20showing%20the%20same%20value%20across%20for%20all%20rows.%20I%20guess%20I%20can't%20directly%20add%20up%20the%20totals%20like%20that%20to%20get%20the%20sum.%20I%20might%20need%20to%20unpivot%20the%20monthly%20quantities%20and%20respective%20monthly%20costs.%20But%20not%20sure%20how%20to%20do%20it%20and%20continue%20ABC%2C%20XYZ%20analysis.%20Any%20help%20from%20experts%3F%20If%20so%2C%20please%20let%20me%20know.%20Thanks%20a%20ton%20in%20advance.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1747510%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%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1748874%22%20slang%3D%22en-US%22%3ERe%3A%20Power%20Pivot%20-%20Error%20in%20Summary%20calculations%20with%20Cross%20tab%20for%20ABC%2C%20XYZ%20Analysis%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1748874%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F795024%22%20target%3D%22_blank%22%3E%40prasad1211%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20seen%20your%20dataset%20%26amp%3B%20your%20data%20need%20to%20be%20normalize%20using%20Power%20Query%20then%20you%20can%20load%20your%20data%20into%20the%20data%20model%20to%20do%20rich%20analysis.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EKindly%20find%20attached%20is%20your%20file%20with%20change%20done%20to%20it.%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%20image-alt%3D%22Snag_4b859084.png%22%20style%3D%22width%3A%20639px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F224373iEE38BF7D910B8129%2Fimage-dimensions%2F639x249%3Fv%3D1.0%22%20width%3D%22639%22%20height%3D%22249%22%20title%3D%22Snag_4b859084.png%22%20alt%3D%22Snag_4b859084.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3ERegards%2C%20Faraz%20Shaikh%20%7C%20MCT%2C%20MIE%2C%20MOS%20Master%2C%20Excel%20Expert%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CEM%3E%3CFONT%20color%3D%22%23808080%22%3EIf%20you%20find%20the%20above%20solution%20resolved%20your%20query%20don't%20forget%20mark%20as%26nbsp%3B%3CSPAN%3EOfficial%2FBest%20Answer%20and%20like%20to%20help%20the%20other%20members%20find%20it%20more%3C%2FSPAN%3E%3C%2FFONT%3E%3C%2FEM%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1749415%22%20slang%3D%22en-US%22%3ERe%3A%20Power%20Pivot%20-%20Error%20in%20Summary%20calculations%20with%20Cross%20tab%20for%20ABC%2C%20XYZ%20Analysis%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1749415%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%2F156456%22%20target%3D%22_blank%22%3E%40Faraz%20Shaikh%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks.%20The%20challenge%20is%20to%20convert%20the%20cross%20tab%20information%20into%20the%20list%20so%20as%20to%20be%20able%20to%20upload%20into%20the%20Power%20Pivot%20or%20Power%20BI%20for%20subsequent%20analysis.%20I%20am%20not%20sure%20how%20it%20can%20be%20done.%20My%20actual%20files%20are%20quite%20huge%20with%20the%202000%2B%20product%20sales%20across%20all%20the%20months%20in%20the%20year%20with%20sales%20qty%20in%20Kg%20and%20sales%20value%20in%20Euro%20in%20the%20cross%20tab%20fashion%20as%20I%20showed%20in%20the%20example%20file.%26nbsp%3B%20This%20transformation%20needs%20to%20be%20done%20for%20all%20the%20warehouses%20for%20all%20countries%20which%20come%20to%20be%20about%2050%2B%20files.%20I%20need%20to%20have%20a%20mechanism%20to%20automate%20transformation%20for%20all%20these%20files.%26nbsp%3B%3C%2FP%3E%3CP%3EI%20adopted%20one%20in-direct%20method%20which%20may%20not%20be%20the%20best%3C%2FP%3E%3CP%3E1.%20Copied%20and%20created%20an%20identical%20copy%20of%20the%20same%20file.%20Now%20I%20have%20two%20files%20of%20the%20same%20info%3C%2FP%3E%3CP%3E2.%20In%20the%20first%20file%2C%20I%20removed%20the%20Sales%20in%20Euro%20values%20and%20kept%20only%20the%20characteristics%20and%20Kg%20values%3C%2FP%3E%3CP%3E3.%20I%20unpivoted%20the%20Kg%20values%20and%20split%20the%20attribute%20column%20into%20two%20and%20separated%20the%20month%20and%20year%20information%20in%20one%20column.%26nbsp%3B%3C%2FP%3E%3CP%3E4.%20Similarly%2C%20I%20removed%20the%20Sales%20in%20Kg%20values%20from%20the%20second%20file%20and%20kept%20only%20the%20characteristics%20and%20Euro%20values%3C%2FP%3E%3CP%3E5.%20I%20unpivoted%20the%20Euro%20values%20and%20split%20the%20attribute%20column%20into%20two%20and%20separated%20the%20month%20and%20year%20information%20in%20one%20column.%26nbsp%3B%3C%2FP%3E%3CP%3E6.%20I%20added%20both%20the%20sheets%20into%20the%20model%20and%20linked%20them%20with%20Product%20ID.%26nbsp%3B%3C%2FP%3E%3CP%3E7.%20Now%20there%20are%20two%20tables%20but%20not%20sure%20it%20is%20the%20right%20way%20or%20not%20and%20gives%20the%20ABC%2C%20XYZ%20analysis.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EExperts%2C%20please%20suggest%20if%20there%20is%20any%20simpler%20way%20to%20do%20it.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Occasional Contributor

Capture.PNG

 

Hi
I have a crosstab excel file with columns of Product, Product description, Sale in Kg for 12 months in 12 columns, corresponding Sale in Value (Euros) for 12 months in the next 12 columns. I made this base sheet as a Table and added to Power pivot. Now I need to do the ABC, XYZ analysis based on the qty and Value. Taking the last 12 months, I am trying to calculate the yearly total sum of the quantity in Kg and the yearly total sum of value in Euros. In the add column in power pivot, I used a Sum function, selected the 12 months Sales Kg values from dropdown and another add column, again used the Sum function, selected 12 months Sale in Value (Euro). But the values are not totaling up and showing the same value across for all rows. I guess I can't directly add up the totals like that to get the sum. I might need to unpivot the monthly quantities and respective monthly costs. But not sure how to do it and continue ABC, XYZ analysis. Any help from experts? If so, please let me know. Thanks a ton in advance.

2 Replies
Highlighted

Hi @prasad1211 

 

I have seen your dataset & your data need to be normalize using Power Query then you can load your data into the data model to do rich analysis.

 

Kindly find attached is your file with change done to it.

 

Snag_4b859084.png

 

Regards, Faraz Shaikh | MCT, MIE, MOS Master, Excel Expert

If you find the above solution resolved your query don't forget mark as Official/Best Answer and like to help the other members find it more

Highlighted

Hi @Faraz Shaikh 

 

Thanks. The challenge is to convert the cross tab information into the list so as to be able to upload into the Power Pivot or Power BI for subsequent analysis. I am not sure how it can be done. My actual files are quite huge with the 2000+ product sales across all the months in the year with sales qty in Kg and sales value in Euro in the cross tab fashion as I showed in the example file.  This transformation needs to be done for all the warehouses for all countries which come to be about 50+ files. I need to have a mechanism to automate transformation for all these files. 

I adopted one in-direct method which may not be the best

1. Copied and created an identical copy of the same file. Now I have two files of the same info

2. In the first file, I removed the Sales in Euro values and kept only the characteristics and Kg values

3. I unpivoted the Kg values and split the attribute column into two and separated the month and year information in one column. 

4. Similarly, I removed the Sales in Kg values from the second file and kept only the characteristics and Euro values

5. I unpivoted the Euro values and split the attribute column into two and separated the month and year information in one column. 

6. I added both the sheets into the model and linked them with Product ID. 

7. Now there are two tables but not sure it is the right way or not and gives the ABC, XYZ analysis. 

 

Experts, please suggest if there is any simpler way to do it.