Attached is the spreadsheet which I'm referencing.
Column A contains different items.Since this is an example I'm showing 10 items but in real data I have around 1000s of items.
Column B, C,D, E are the metrics for the corresponding items.
Each metric has a different goal and this goal varies with items ( Eg: Chocolates; Sales Goal: 15000, Monitor: Sales Goal: 30000, Pens Sales Goal: 40000, Cord Quality Goal%: 5%). Now if I want to show a calculation like Sales Adherence=Actual Sales amount /Goal, what is the best way to input that Goal number. Below are the different ways I thought I could achieve this and I'm also listing what are the pain points for each method.
1) Have a parameter called Goal setup for each of the metric ( Sales amount, Quantity, Quality %, Compliance %). So whenever I bring the metric into the view it asks me for the Goal. Although this is one solution I can't imagine myself inputting these goals every time I change the item type into the view.
2) Create a calculated item for Goals: Sales Goal:: and within that field write a bunch of If statements saying If Item == Chocolates, Sales Goal = 15000 or If item == Monitor, Sales Goal == 30000 and so on... I could do this but again it it the same story of writing thousands of If statements.
3) Have those goals expressed in the excel spreadsheet itself. This is not feasible as the goals keep changing every week so I can't set it to a standard goal number in excel.
I know there is a better solution for this and hence posting this question so I could get others views .Can you please help me in figuring this out?
Message was edited by: Ashwin Gudipati
Added the Original workbook. Here is what I'm trying to do.. Each AUX Measure has a different goal depending on the CTcode field... I tried to create a calculated field called AUX0%Goal and within the calculated field specifying conditions like If CTCode = 08_SEP then AUX0% Goal is 0%. However when I'm trying to put the AUX0% Goal as a label, the number is being shown on all metrics in the view which I don't want. I want the AUX0%Goal label to be shown up only on the AUX0% measure, Similary I would like to show the AUX1%Goal label only on the AUX1% measure. Does that make sense?