Pivot Calc Help

[einnacelam]

Hi All

I'm trying to add a calculation field to work out the % of two items in my pivot

However, when I try to insert the calc field, i'm unable to work with the columns I need to work with

My data set consists of individual training records, each row consisting of an e-learning module that has been assigned to someone. My pivot is organised, breaking down the status data into different categories - e.g. complete, overdue etc

I need to get the % complete by dividing the "complete" by "grand total" in each row but the analyze/calculated field function is only letting me use status and not the categories within status so I'm unable to figure out completion rate.

Sample data:

Name / Module Name / Status
Sam / Health & Safety / Overdue
Tom / Food Hygiene / Complete
Sarah / Allergens / Failed
Judy / Food Hygiene / Due

Pivot is names in rows with subset of status as the column labels giving data looking like this:

Name / Complete / Due / Overdue / Started / Failed / Grand Total

Sam / 0 / 2 / 1 / 0 / 0 / 3

(sorry my snipping tool isn't wanting to play ball)

Any advice is appreciated

Many thanks
Annie

 

[awoohaw]

@einnacelam

The snipping/image pasting tool does not really help the forum help you. Mr. Excel has a tool called xl2bb add in (link below) that allows you to post mini worksheets of your data and formulas (link below). Please do that with an anonymized portion of your worksheet. If you cannot use the tool, then pasting data in a table (with rows and columns labeled) and your formulas and expected results is the next best. Images require the forum to completely recreate your scenario which not only takes time but also can have errors in input values and assumptions. And, you'll get MUCH quicker results.

Thanks in advance.