KPI with different targets

beginexcel

New Member
Joined
Mar 3, 2016
Messages
3
Hi,

I have no problem setting up KPI using set value. However, I have different targets every month. I can't just change the set value every time I want to view the month. Please help. Any Idea how I can approach this?

I tried adding target value to my raw data and sum that column as one of my calculated field. Then, when I set up my KPI, I use that calculated field as my target. But, it doesn't work.

Any suggestion and advice are appreciated. Thank you :)
 

Some videos you may like

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.

Gerald Higgins

Well-known Member
Joined
Mar 26, 2007
Messages
9,115
Hi. After reading your OP several times, I have no clue as to what exactly you want to do.

How about giving us a small sample of your date (make it up if you don't want to post real data), and describe EXACTLY what you want to do with it, and what you want the results to be.
 

beginexcel

New Member
Joined
Mar 3, 2016
Messages
3
Hi Gerald,

Thank you so much for the reply and the very sorry about the confusion.
I am not so great at explaining. So Here, I provided an example.

Say I have 2 teams and each team has 4 agents.
Here's the Raw data :

Agent Team Total Survey Received Excellent Survey Received
A Team1 10 8
B Team1 15 11
C Team1 20 16
D Team1 9 7
E Team2 16 10
F Team2 12 10
G Team2 8 8
H Team2 14 11

Then I did a pivot tables and added my data into data model.
I added a calculated field to calculate the survey score, which is "Excellent Survey received" / "Total Survey Received".
Then I added a KPI for the survey score. I used absolute value as my target. (in this example, my target is 75%)

Row Labels Survey Score Survey Score Status
Team1
A 80.00 % 1
B 73.33 % 0
C 80.00 % 1
D 77.78 % 1
Team2
E 62.50 % -1
F 83.33 % 1
G 100.00 % 1
H 78.57 % 1





So here's my problem! I have different targets every month. It would be inefficient if I go into my KPI and change my absolute value every time I view different month. It would also be too much if I create a table for each month. ( I have several KPI in one Excel doc)
The raw data only shows one month worth of data. But what happens if I add more data in?
Is there a way where I can set up different KPI for different months but within the same raw data table.

Please help! Thanks :)
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,235
Office Version
  1. 365
Platform
  1. Windows
Eh, this is a really stupid question but what is a 'KPI'?
 

Watch MrExcel Video

Forum statistics

Threads
1,123,492
Messages
5,601,994
Members
414,490
Latest member
Rip181

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top