Forum Discussion
percentage of sales advance in pivot table
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.
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
8 Replies
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?
Subodh_Tiwari_sktneer, sorry, now I understand, does the attached file is useful to you?
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?
- Excel 365, should be the version from 2016/2017, not sure. How is that sample file that you refer?
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
