Percenages in Powerpivot Grand total is not producing correct result

Hi all
I have this annoying issue and am going round the circle trying to figuring it out and any help is appreciate it.

I have created a power pivot which in brief contains department name, divison, Sales Person,KPI score, Target (which is diffrent for different departments).

I have added a calculated column which calculated the percentage of the KPI score for each salesperson for department within the division (used formiula KPI score/KPI Traget).

When I create my Pivot report from the power pivot the I add the department name and division in row labels and in values field I add the KPi Score, Target (both as sum, ai it gives me the total number for Target and total KPI number achieved for each department within the division). I also add the percentage clolumn and change the value field settings to average. This will show the right percentage for indivisual departments however when I see the grand Total it does not show the right percentage.

I need the Grand Total column to show the total of the Targets for all divisions, Total KPI score for all divisons and then to calculate the percentage as Total KPI SCore/Total KPI Target instaed of working out the average of what is in Percentage column. Same thing for the KPI percentages for each department i.e in the listing of the report it under percentage it should be equal to (kpi Score/kpi target)

I really need this urgently as I have a dealine to meet and am going rounf circle. I know there is a probem with my logic in working out the percentages but cant see why, it also does not allow me to calcualate this field manually

Hope this makes sense and any help is appreciate it

yes , i need a loumn which works out the percentage s using the formulka, i have done this but when in loist them in the pivot report i am not sure how to represent the dat so the the gran totals will be correct. I= need to show the score , target and % achieved for each depqatment within the division and then in the grand total it works out the right percentage for the whole ting i.e. total score/total target for across the divisions and departments. Basically what measure do i use to show th epercentages so t ecalculaions is correct

im using a calculted column

Can you let me know how to do this. When I include the percentage column what .easier do I use to show the results .

Dear JBeaucaire

I managed to figure out how to use measures in PowerPivot and it works absolutley fine now.

Thank you so much for all your help and advise.
This forum has been my lifesaver for a long time now