• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

formula for pivot calculation

jayalaxmi

Active Member
Hi Team,

Request you to help with the formula calculation in the attached sample file.

Also, is there a way to use the shared file (formula table) in my other excel dashboard workbook.

Regards
Jaya
 

Attachments

  • sample.xlsx
    64.6 KB · Views: 7
Perhaps using getpivotdata function? See attached.
Couple of things:
- do not add dimensions to the pivot. It will break the formula.
- ensure labels used in the table do reflect the labels form the pivot.

How this is done? Simply refer to a pivot cell that holds measures. Then replace the hard coded labels with cell references. Wrapped in IFERROR to get rid of error values when dimensions are not present in the pivot table.
 

Attachments

  • Copy of sample_getpivotdata.xlsx
    65 KB · Views: 9
Perhaps using getpivotdata function? See attached.
Couple of things:
- do not add dimensions to the pivot. It will break the formula.
- ensure labels used in the table do reflect the labels form the pivot.

How this is done? Simply refer to a pivot cell that holds measures. Then replace the hard coded labels with cell references. Wrapped in IFERROR to get rid of error values when dimensions are not present in the pivot table.
Thank you for your reply.

It seems you have linked the formulas with the pivot table as when I change the country to New Zealand. It is not coming up with proper output.

It would be better to update or link formulas based on the Raw data sheet according to the pivot table filter criteria.

Regards
Jaya
 
Thank you for your reply.

It seems you have linked the formulas with the pivot table as when I change the country to New Zealand. It is not coming up with proper output.

It would be better to update or link formulas based on the Raw data sheet according to the pivot table filter criteria.

Regards
Jaya
Hi, when I change the country in the filters, the pivot is updated and thus ... also the formulae I used. Not sure I see where the problem is.
60480
60481
 
Hi, when I change the country in the filters, the pivot is updated and thus ... also the formulae I used. Not sure I see where the problem is.
View attachment 60480
View attachment 60481
This seems to work fine.
But actually, in my dashboard there are 4 different graphs for which the numbers comes from the above pivot, eg. AUS -Payroll management and AUS - Talent & services and same as for NZ. so can u help me achieving that results. I need to link that tables to graph may be i need to create 4 different tables for the same.

Regards
Jaya
 
Sounds like those extra charts are not made as pivot charts then? Without the actually workbook, that's rather hard for me to tell.
 
Sounds like those extra charts are not made as pivot charts then? Without the actually workbook, that's rather hard for me to tell.
Yes you are right..they are not linked to pivots. But the numbers are picked up from pivots. I had created 4 different tables for the same(In the filter option country should be AUS and category should be Payroll management and the same for NZ as well. As of now I am doing it manually. so I am trying to automate that part. It would be of great help if you suggest some.

Something of this sort. Hope it helps!!

60611

60612
Regards
Jaya
 
Why don't you simply make all the pivot tables you'd need, based on the same pivot cache (simply copy/paste the same pivot over and over again)?
Then either turn those into pivot charts or use your intermediate tables.

For sharing the same data in different workbooks, you can refer a pivot table to external data.
60617
 
Back
Top