Sep 27 2019 05:18 PM
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.
Sep 27 2019 08:29 PM
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?
Sep 27 2019 10:14 PM
Sep 28 2019 12:06 AM
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.
Sep 28 2019 02:50 AM
SolutionIf 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
Sep 28 2019 02:57 PM
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?
Sep 28 2019 03:12 PM
@Subodh_Tiwari_sktneer, sorry, now I understand, does the attached file is useful to you?
Sep 28 2019 09:18 PM - edited Sep 28 2019 09:19 PM
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?
Sep 29 2019 05:54 AM
That's just the math, it's necessary to dig your actual data to understand why do you have 2% difference
Sep 28 2019 02:50 AM
SolutionIf 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