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?
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?
That's just the math, it's necessary to dig your actual data to understand why do you have 2% difference
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.