SOLVED
Home

percentage of sales advance in pivot table

%3CLINGO-SUB%20id%3D%22lingo-sub-880204%22%20slang%3D%22en-US%22%3Epercentage%20of%20sales%20advance%20in%20pivot%20table%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-880204%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%20people!%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYesterday%20I%20was%20trying%20to%20make%20a%20pivot%20table%20that%20shows%20%22presales%22%2C%20%22sales%22%20and%20de%20%25%20advance%20%5Bwich%20means%20%22sales%2Fpresales%22%5D.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ETo%20be%20clear%2C%20in%20my%20work%20we%20sell%20packages%20to%20the%20retailers%20and%2C%20I%20want%20to%20track%20%22how%20much%20%25%20of%20the%20packages%20the%20retailer%20hav%20sell%22.%20Lets%20say%20the%20retailr%20bougth%2010%20packages%20and%20sold%208%20to%20customers%2C%20so%2C%20we%20understand%20the%20retailer%20sold%20the%2080%25%20of%20the%20presale%3C%2FP%3E%3CP%3E%3CBR%20%2F%3EDoes%20somebody%20know%20how%20can%20I%20show%20this%203%20data%20on%20a%20pivot%20table%3F%20Consider%20that%20my%20database%20shows%20in%20one%20column%20the%20kind%20of%20sale%20(presale%20or%20sale)%20and%2C%20in%20another%20column%20shows%20the%20amount.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-880204%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-880368%22%20slang%3D%22en-US%22%3ERe%3A%20percentage%20of%20sales%20advance%20in%20pivot%20table%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-880368%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F297964%22%20target%3D%22_blank%22%3E%40Carlos_Gomez%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhy%20not%20upload%20a%20small%20sample%20file%20with%20some%20dummy%20data%20to%20visualize%20the%20layout%20of%20your%20data%20in%20order%20to%20provide%20an%20accurate%20solution%3F%3C%2FP%3E%3CP%3EBtw%20what%20Excel%20version%20you%20are%20using%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-880405%22%20slang%3D%22en-US%22%3ERe%3A%20percentage%20of%20sales%20advance%20in%20pivot%20table%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-880405%22%20slang%3D%22en-US%22%3EExcel%20365%2C%20should%20be%20the%20version%20from%202016%2F2017%2C%20not%20sure.%20How%20is%20that%20sample%20file%20that%20you%20refer%3F%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-880427%22%20slang%3D%22en-US%22%3ERe%3A%20percentage%20of%20sales%20advance%20in%20pivot%20table%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-880427%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F297964%22%20target%3D%22_blank%22%3E%40Carlos_Gomez%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBy%20sample%20file%20I%20meant%20the%20Excel%20Workbook%20with%20some%20data%20to%20work%20with.%3C%2FP%3E%3CP%3EWhen%20you%20click%20on%20Reply%2C%20a%20paper%20clip%20icon%20appears%2C%20click%20that%20to%20open%20the%20upload%20window%20and%20attach%20the%20sample%20file.%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20785px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F133929i0E62399DD61C56B7%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22Upload%20File.jpg%22%20title%3D%22Upload%20File.jpg%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-880537%22%20slang%3D%22en-US%22%3ERe%3A%20percentage%20of%20sales%20advance%20in%20pivot%20table%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-880537%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F297964%22%20target%3D%22_blank%22%3E%40Carlos_Gomez%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIf%20your%20data%20is%20like%20this%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20444px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F133939iB685122B266E4D1D%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22image.png%22%20title%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3Eto%20create%20PivotTable%20as%20on%20the%20right%3C%2FP%3E%0A%3CP%3E-%20add%20data%20to%20data%20model%20creating%20PivotTable%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20288px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F133940iB432BAD6189ADDB4%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22image.png%22%20title%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E-%20add%20three%20measures%20to%20the%20data%20model%2C%20using%20PowerPivot%20or%20from%20right%20click%20menu%20on%20Table%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20320px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F133941i4FC9E934E2A54361%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22image.png%22%20title%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3EMeasures%20are%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3ESale%3A%3DCALCULATE(SUM(Table1%5BAmount%5D)%2CTable1%5BKind%5D%3D%22sale%22)%0APresale%3A%3DCALCULATE(SUM(Table1%5BAmount%5D)%2CTable1%5BKind%5D%3D%22presale%22)%0ASales%20%25%3A%3DDIVIDE(%5BSale%5D%2C%5BPresale%5D)%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E-%20add%20them%20to%20values%20of%20the%20PivotTable%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20389px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F133943iE7754EEEF3083B82%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22image.png%22%20title%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E-%20apply%20formatting%20and%20design%20as%20desired%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-880885%22%20slang%3D%22en-US%22%3ERe%3A%20percentage%20of%20sales%20advance%20in%20pivot%20table%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-880885%22%20slang%3D%22en-US%22%3E%3CP%3EThanks%20a%20lot%2C%20%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%26nbsp%3B!%20It%20worked%20perfectly%2C%20but%2C%20for%20those%20retailers%20who%20sold%20the%20total%20of%20presale%20the%20pivot%20table%20shows%20me%20in%20percentage%20102%2C08%25%20or%20102%2C04%25%20and%2C%20for%20those%20who%20sold%2098%25%20the%20pivot%20table%20shows%20as%20100%25%2C%20I%20mean%2C%20it%20has%20a%20difference%20of%202%25%2C%20does%20this%20occurs%20because%20of%20my%20database%20or%20is%20just%20an%20adjustment%20I%20need%20to%20do%20in%20the%20pivot%20table%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-880907%22%20slang%3D%22en-US%22%3ERe%3A%20percentage%20of%20sales%20advance%20in%20pivot%20table%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-880907%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F394231%22%20target%3D%22_blank%22%3E%40Subodh_Tiwari_sktneer%3C%2FA%3E%2C%26nbsp%3Bsorry%2C%20now%20I%20understand%2C%20does%20the%20attached%20file%20is%20useful%20to%20you%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-881105%22%20slang%3D%22en-US%22%3ERe%3A%20percentage%20of%20sales%20advance%20in%20pivot%20table%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-881105%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F297964%22%20target%3D%22_blank%22%3E%40Carlos_Gomez%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAs%20per%20your%20sample%20data%2C%20Retailer1%20has%2053%2C800%20as%20Presale%20Qty%20and%2051%2C508%20as%20Sale%20Qty%2C%20therefore%20Retailer1%20sold%2095.74%25%20Presale%20Qty.%20Is%20it%20incorrect%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-881235%22%20slang%3D%22en-US%22%3ERe%3A%20percentage%20of%20sales%20advance%20in%20pivot%20table%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-881235%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F297964%22%20target%3D%22_blank%22%3E%40Carlos_Gomez%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThat's%20just%20the%20math%2C%20it's%20necessary%20to%20dig%20your%20actual%20data%20to%20understand%20why%20do%20you%20have%202%25%20difference%3C%2FP%3E%3C%2FLINGO-BODY%3E
Carlos_Gomez
Occasional Contributor

Hi, people! 

 

Yesterday I was trying to make a pivot table that shows "presales", "sales" and de % advance [wich means "sales/presales"].

 

To be clear, in my work we sell packages to the retailers and, I want to track "how much % of the packages the retailer hav sell". Lets say the retailr bougth 10 packages and sold 8 to customers, so, we understand the retailer sold the 80% of the presale


Does somebody know how can I show this 3 data on a pivot table? Consider that my database shows in one column the kind of sale (presale or sale) and, in another column shows the amount.

8 Replies

@Carlos_Gomez 

Why not upload a small sample file with some dummy data to visualize the layout of your data in order to provide an accurate solution?

Btw what Excel version you are using?

Excel 365, should be the version from 2016/2017, not sure. How is that sample file that you refer?

@Carlos_Gomez 

By sample file I meant the Excel Workbook with some data to work with.

When you click on Reply, a paper clip icon appears, click that to open the upload window and attach the sample file.

Upload File.jpg

Solution

@Carlos_Gomez 

If your data is like this

image.png

to create PivotTable as on the right

- add data to data model creating PivotTable

image.png

- add three measures to the data model, using PowerPivot or from right click menu on Table

image.png

Measures are

Sale:=CALCULATE(SUM(Table1[Amount]),Table1[Kind]="sale")
Presale:=CALCULATE(SUM(Table1[Amount]),Table1[Kind]="presale")
Sales %:=DIVIDE([Sale],[Presale])

- add them to values of the PivotTable

image.png

- apply formatting and design as desired

Thanks a lot, @Sergei Baklan ! It worked perfectly, but, for those retailers who sold the total of presale the pivot table shows me in percentage 102,08% or 102,04% and, for those who sold 98% the pivot table shows as 100%, I mean, it has a difference of 2%, does this occurs because of my database or is just an adjustment I need to do in the pivot table?

@Subodh_Tiwari_sktneer, sorry, now I understand, does the attached file is useful to you?

@Carlos_Gomez 

As per your sample data, Retailer1 has 53,800 as Presale Qty and 51,508 as Sale Qty, therefore Retailer1 sold 95.74% Presale Qty. Is it incorrect?

 

 

@Carlos_Gomez 

That's just the math, it's necessary to dig your actual data to understand why do you have 2% difference

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
29 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies