cancel
Showing results for
Did you mean:

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

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.

Announcements

#### Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

#### Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

#### Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors