|
| Pivot Table Calculated Value |
| Iniciado por SolutionJ-Reg, 19,oct. 2017 23:37 - 8 respuestas |
| |
| | | |
|
| |
Publicado el 19,octubre 2017 - 23:37 |
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 |
| |
| |
| | | |
|
| | |
| |
Publicado el 20,octubre 2017 - 16:32 |
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 |
| |
| |
| | | |
|
| | |
| |
Publicado el 20,octubre 2017 - 19:05 |
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 |
| |
| |
| | | |
|
| | |
| |
Publicado el 20,octubre 2017 - 21:10 |
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 |
| |
| |
| | | |
|
| | |
| |
Publicado el 20,octubre 2017 - 21:50 |
Hi,
Unfortunately that won't work when calculating percentages Allard, but thanks... |
| |
| |
| | | |
|
| | |
| |
Publicado el 23,octubre 2017 - 12:59 |
Hi Reg,
Could you code the percentage calculation using the pvtPosition variable and the Display Cell event?
Thanks
Ned |
| |
| |
| | | |
|
| | |
| |
Publicado el 23,octubre 2017 - 13:07 |
Hi Ned,
That looks very interesting, I'll give it a go.
Thanks  |
| |
| |
| | | |
|
| | |
| |
Publicado el 16,febrero 2023 - 12:11 |
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? |
| |
| |
| | | |
|
| | |
| |
Miembro registrado
23 mensajes |
|
Publicado el 16,febrero 2023 - 22:17 |
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. |
| |
| |
| | | |
|
| | | | |
| | |
|