Forum Discussion
percentage of sales advance in pivot table
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
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
Thanks a lot, SergeiBaklan ! 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?
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.
