cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Regular Visitor

## How to add variable percentage reduction to different products

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.

1 ACCEPTED SOLUTION
Super User
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=

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.
2 REPLIES 2
Super User
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=

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.
Regular Visitor

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.