percentage of sales advance in pivot table

[Carlos_Gomez]

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.

[#2]

@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?

[#3]

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

[#4]

@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.

[#5 · Accepted solution]

@Carlos_Gomez 

If your data is like this

to create PivotTable as on the right

- add data to data model creating PivotTable

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

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

- apply formatting and design as desired

[#6]

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?

[#7]

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

[#8]

@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?

[#9]

@Carlos_Gomez 

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