• 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.

Pivot table - calculated item greyed out

H

Hayley

Member
Hi there,
I have a pivot table with values to include count of leads and count of bookings. I want to have a calculated item that shows conversion rate, which would be ="count of bookings"/"count of leads" but the calculated item is greyed out.

How would I do this otherwise, and why is it greyed out?

Thank you
 
Attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.
 
Hayley, would you happen to be working in a datamodel (powerpivot)? Then you can use DAX measures. Calculated fields and items are not available in powerpivot.
Like AlanSidman suggests, a workbook can help us, help you, better and faster.
 
I'm not using powerpivot, just a regular simple pivot table. Here is a sample of the columns that would be a part of the calculated item, and a column that I manually calculated but am trying to create *as* the calculated item.

The original data shows a Lead ID number. The count of Lead IDs shows me how many leads came in. The original data also shows how many passengers ended up booking for each lead. If there are ANY passengers, that means the lead turned into a booking. So if I count how many leads had passengers (i.e. as opposed to summing up the number of passengers), then that tells me how many bookings there were.

I want a calculated item to show me the penetration...# bookings/#leads.

Thanks
 

Attachments

  • sample.xlsx
    34.7 KB · Views: 6
Hayley
1) with another pivot-table
2) ... hmm ... don't seems to be correct ... ?
Did You notice that 'the new pivot-table' values in different order?
Check one more time...
 
vletm said:
Hayley
1) with another pivot-table
2) ... hmm ... don't seems to be correct ... ?
Did You notice that 'the new pivot-table' values in different order?
Check one more time...
Click to expand...
Ah, no I didn't notice that. So you did another pivot table using the first pivot table as the data? When I try to add a calculated item I get a message "References to multiple item names per field cannot be included in the PivotTables formula", so it has to be a calculated field?

And is there any way to do it and keep it connected to the original pivot table so I'm showing just one table when I send it off to the powers that be?
 
Hayley
This is one my sample to show how would You get Your 'the penetration's.

Excel has some rules how would it work
.. it would be sometimes different way, than You would like to do.
Maybe You tried something 'Your way'... hmm?

Your original data is 'somewhere'.
Could You do that calculations from Your 'original data'?
( not from Pivot-table itself? )
 
vletm said:
Hayley
This is one my sample to show how would You get Your 'the penetration's.

Excel has some rules how would it work
.. it would be sometimes different way, than You would like to do.
Maybe You tried something 'Your way'... hmm?

Your original data is 'somewhere'.
Could You do that calculations from Your 'original data'?
( not from Pivot-table itself? )
Click to expand...
I can't do it from the original data because the original data shows Lead IDs as actual ID numbers. So it might be like 57676, 57677, 57678 and I needed the pivot table to count them so that in the pivot table the leads column will be 3. And the same with passengers...it might show 5 passengers, 7 passengers, 2 passengers. I need the pivot table to count them so that the bookings column shows 3. And then I want the calculated item to say the penetration for that particular campaign is 100% (3 bookings/3 leads).
 
vletm said:
Hayley
Without new information ... so far, I cannot figure other way?
Click to expand...
I don't think I have any new information, but are you able to explain to me *why* using a calculated item is not an option? What is it about this particular table that doesn't allow me to use it?
 
*why*
For me *why*
... because You try to use 'results before there are results'.

There are 'fields' which can use with formulas with limited functions.
Even Excel has rules, how it would work.
Sometimes user would like to use Excel other way.

'New information' could be eg that original data ... do it help - who knows?
 
From contextures' website (Debra Dalgleish)
Limitations
  • Calculated field formulas cannot refer to the pivot table totals or subtotals
  • Calculated field formulas cannot refer to worksheet cells by address or by name.
  • Sum is the only function available for a calculated field.
  • Calculated fields are not available in an OLAP-based pivot table.
 
You must log in or register to reply here.
Back
Top