How to add variable percentage reduction to different products

[CHR15F1T2]

Hi folks, newbie so pls go easy, still finding my feet.

I have a data table which contains product sales.  I have created measures to consider year-on-year change in sales performance and against 3-year average.  The one thing I'm struggling to do is show how each product is performing against target.  On 1 product I have to increase sales by 3% against the 3-yr ave but with another it's 10%.  Some products I've only to improve sales versus previous year.  Can I create 1 measure that uses different % change targets or do I need to do this in my source data?  Hope that makes sense.

[speedramps · Accepted solution]

Hi Chris 

This assumes you have 1 record per product per year (otherwise average calculation wont give desired results)

Create a list of target percentages. 

Product, target +/- %, based on years

A,10,3

B,5,1

C,3,2

D,-12,3

Eg

product D decrease -12% based on 3 year ave

Product B increase 5% based on previous 1 year average

Create a 1:m relationship to your sales table

Create measure

Forecastsales =

VAR mybaseyears = SELECTEDVALUE(targets[baseyear])

VAR mybasedate=

DATEADD(TODAY(),- mybaseyears,year)

VAR myave =

CALCULATE(

AVE(yourtable[sales],

ALLEXCEPT(yourtable[product],

yourtable[date] >= mybasedate

)

VAR uplift = 

1+

(SELECTEDVALUE(targets[target])

/100) 

RETURN 

myave * uplift

Please click thumbs up and accept as solution.

One problem per ticket. If you need to refine your problem then please accept this solution and raise new tickets. Thanks.

[CHR15F1T2]

Many thanks for such a prompt response @speedramps .  I have a seperate SQL view that has the product sales aggregated so I will do as you suggest using that approach.  Very grateful for your time.