How to create a measure for Target

[Mavericks334]

Hi,

I have two tables, One with the Tenure of the employees and a target table

The tenure for each employee varies.

How ever the target table has only three ranges[TABLE="width: 141"]
<COLGROUP><COL style="WIDTH: 95pt; mso-width-source: userset; mso-width-alt: 4644" width=127><COL style="WIDTH: 46pt; mso-width-source: userset; mso-width-alt: 2230" width=61><TBODY>[TR]
[TD="width: 127"]Tenure[/TD]
[TD="width: 61"]Target[/TD]
[/TR]
[TR]
[TD]0 - 0.99 [/TD]
[TD="align: right"]30[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]1 - 1.99 Years[/TD]
[TD="bgcolor: transparent, align: right"]35[/TD]
[/TR]
[TR]
[TD]>1.99[/TD]
[TD="align: right"]40[/TD]
[/TR]
</TBODY>[/TABLE]

How do i get the target for each individual.

 

[pjmorris]

Hi,

Try this formula: =if(A1>1.99, 40,if(A1<1,30,35))

If you think you may change the values of the targets, then if you enter the three values in cells B1 to B3 on a sheet named TARGET the formula might be: =IF(A1>1.99,TARGET!B3,IF(A1<1,TARGET!B1,TARGET!B2))

Hope this helps

 

[Mavericks334]

Thank you, for the above formula i need to create a calulated column, i wish to created a calculated measure that would reduce my workbook size

 

[pjmorris]

Thats fine, assuming that you want the calculated column in Column B and that the table is on the TARGET sheet as described above do the following:

1. In cell B1: =IF(A1>1.99,TARGET!$B$3,IF(A1<1,TARGET!$B$1,TARGET!$B$2))
2. Copy the cell down the column as far as you need.

If you have column headings and the top cell in which you enter the formula is in row 8 for example: =IF(A8>1.99,TARGET!$B$3,IF(A8<1,TARGET!$B$1,TARGET!$B$2))

Hope this helps.

 

[Mavericks334]

It does not work in powerpivot window. If it was regualar excel it would have, but powerpivot is different.

 

[pjmorris]

Ahh, now you have me - many apologies I thought we were talking regular Excel and I don't know anything about powerpivot.

Sorry

 

[Laurent C]

You can use a FILTER expression on your Target table, or you can use IF or SWITCH expression combined with LOOKUPVALUE.

 

[Mavericks334]

Could you give me an example which i could try and use