Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
krider71
Frequent Visitor

Avg formula works on Non Promoted weeks but not on Promoted weeks to calculate incremental sales...

Hello,

 

My goal is to calculate incremental sales on promotion week compared to a rolling 13wk Non Promoted sales week and ultimately get a lift %.  Will also want to do something similar with the types of Ads we ran but first things first.

 

I have a column in my SalesVT table that is called Promotions and each row is labeled "Non Promo" or "Promo" if the brand was promoted.  I have created a powerbi table that has Non Promo and Promo on the columns and week end down the rows. 

  1. Pulled in the measures Retail Sales by either the Non Promo and Promo week 
  2.  Pulled in a measure that calculates the Non Promoted Rolling 13wk Sales with a measure called Avg Non Promo Retail $ 13Wk Rolling Avg (see formula below) (took me forever to find an formula online that would work and ignore weeks with no non promoted sales). IT actually works and I double checked. The problem is it only works if the Promo and Non Promo filter is on the page which I can live with.
  3. Created a measure called incremental sales (see formula below):  
  4. I get the correct information for the NON Promo Columns but it gives me a totally different Non promo Rolling Avg number under the Promo columns and therefore, incorrect incremental sales.

How do I get the Non Promo/baseline weekly sales to be the same in the Promoted columns as it is in the Non Promo column and calculate the correct incremental sales??

 

Formulas:

 

Avg Non Promo Retail $ 13Wk Rolling Avg =
var Startday= min(Dates[Date])-90
var endday = max(Dates[Date])
return
calculate (Retail Sales],SalesVT[PROMOTIONS]="Non Promo",DATESBETWEEN(Dates[Date],Startday,endday),REMOVEFILTERS(SalesVT[Week Number]))/CALCULATE(DISTINCTCOUNT(SalesVT[Week Number]),DATESBETWEEN(Dates[Date],Startday,endday), REMOVEFILTERS(SalesVT[Week Number]) )

 

Incremental Sales = [Retail Sales]-[Avg Non Promo Retail $ 13Wk Rolling Avg]
 
This is the result:
 
krider71_0-1680307180184.png

 

 

Tried another way:

Created this measure that takes my measure that calculates Retail sales on Promo weeks and it actually calculates the correct incremental sales but UNDER the NonPromo columns, ugh
Incremental Sales 1 = [Retail Ad Promo $]-[Avg Non Promo Retail $ 13Wk Rolling Avg]

 

krider71_2-1680306287236.png

 

Help! And thank you!

 

 

 

 

 

5 REPLIES 5
krider71
Frequent Visitor

Thank you for the reply.  I must be doing something wrong as the formula just gives me the exact same number as my sales for that day and not an average for past weeks.

This is my week rank formula - I even tried the other formula and get the exact same numbers.

 

Week Rank = RANKX('Dates','Dates'[Week of Year],,ASC,Dense) //YYYYWW format

krider71_1-1681269838244.png

 

Not sure what I am doing wrong....

 

@krider71 , what is that last Date = max(Date) , that will not work. I used weekday, so that for Monday  you get all Monday

the (Date) is just my date

 

krider71_0-1681274149929.png

 

@krider71 , yes, I got it. If you say Date = Max([Date]) then we will not get data beyond that row, so remove that, I used weekday, so that I only get the same weekday for the last 13 weeks

amitchandak
Super User
Super User

@krider71 , with help of week rank you can get Avg measure like

 

Last 13 weeks Week Day = CALCULATE(AverageX(Values(Date[Week Rank] ), calculate( sum(Sales[value]))), FILTER(ALL('Date'),'Date'[Week Rank]>=max('Date'[Week Rank])-13 && 'Date'[Week Rank]<=max('Date'[Week Rank]) && 'Date'[WeekDay]=max('Date'[WeekDay])))

 

 

Have these new columns in Date Table, Week Rank is Important in Date/Week Table

Week Rank = RANKX('Date','Date'[Week Start date],,ASC,Dense)
OR
Week Rank = RANKX('Date','Date'[Year Week],,ASC,Dense) //YYYYWW format

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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