|
| Pivot Table Calculated Value |
| Started by SolutionJ-Reg, Oct., 19 2017 11:37 PM - 8 replies |
| |
| | | |
|
| |
Posted on October, 19 2017 - 11:37 PM |
WD22
Hi,
I have a pivot table showing as row values Revenue, Costs, Margin £. I also want to show Margin % based on the calculation Margin % = Margin £ / Revenue.
I have set the Margin % cell to have a local procedure as its source, passing in the Revenue and Margin £ values and giving a result of Margin %, but the results are not always correct.
Has anyone else ever tried anything like this?
Thanks
Reg |
| |
| |
| | | |
|
| | |
| |
Posted on October, 20 2017 - 4:32 PM |
Hi Reg,
I've used the Pivot table quite a bit, but never had much luck using the procedures.
I went down the route of using a calculated field in a query and using the query as a data source - its more reliable for me that way.
Thanks,
Ned |
| |
| |
| | | |
|
| | |
| |
Posted on October, 20 2017 - 7:05 PM |
Hi Ned,
Thanks for the response. I can't use your method in this case because the %,, being a percentage, needs to be calculated in real time as it were based on which row values are expanded or not in the pivot table.
Rgds
Reg |
| |
| |
| | | |
|
| | |
| |
Posted on October, 20 2017 - 9:10 PM |
Hi Reg,
When using a datasource this is exactly what happends. ( ned his option ) The pivot table adds the values of 3 months to the value of a quarter etc.
I have a planning versus control pivot table and this calculates sales etc and compares it with the prognose and displays differences .
regards
Allard |
| |
| |
| | | |
|
| | |
| |
Posted on October, 20 2017 - 9:50 PM |
Hi,
Unfortunately that won't work when calculating percentages Allard, but thanks... |
| |
| |
| | | |
|
| | |
| |
Posted on October, 23 2017 - 12:59 PM |
Hi Reg,
Could you code the percentage calculation using the pvtPosition variable and the Display Cell event?
Thanks
Ned |
| |
| |
| | | |
|
| | |
| |
Posted on October, 23 2017 - 1:07 PM |
Hi Ned,
That looks very interesting, I'll give it a go.
Thanks  |
| |
| |
| | | |
|
| | |
| |
Posted on February, 16 2023 - 12:11 PM |
Hi Reg,
Have you found a solution to this problem? I've been struggling with this for a while now... Can't seem to find a good solution to do this.
The calculated value is also a value, and it says 'No value can be assigned to this type of object.'
For example, I would like to have value, quantity and value/quantity. Not all details have data so I want it calculated on the spot.
Any idea? |
| |
| |
| | | |
|
| | |
| |
Registered member
23 messages |
|
Posted on February, 16 2023 - 10:17 PM |
Hi Erwin
My preference is to use pivot tables in Excel. I extract the raw data I need from whereever, then send it to the data worksheet of an Excel template file, which has a pivot table in a separate worksheet. I set up the template with a few dummy rows of data, and define a dynamic range name covering the data. This range name is the source for the pivot table, and will expand automatically to include new data.
Leaving a few rows of data, which are overriden by the extracted data, ensures that the data range and pivot table will always be valid.
Then all you have to do is display the spreadsheet to the user and refresh the pivot table (can be done with automation code).
The pivot table is pre-defined to include all calculated fields, formatting etc.
Using a template ensures that a copy is created each time, leaving the source template unchanged. |
| |
| |
| | | |
|
| | | | |
| | |
|