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

## Lift of sales based on promo activity

Hi All

I want to calculate lift of sales bsaed on promo activity. Activities are on week basis

i have the data at an order level as follows

 SKUid Activity Date(Week) Sales 10 A 22-02-2022 150 20 A 22-02-2022 100 15 B 15-10-2022 40 10 no 2-08-2022 5 25 B 15-10-2022 15 20 no 2-08-2022 50 25 no 15-01-2022 10 10 no 6-05-2022 15

Now i need to calculate lift of activity(avg sales per week) when skus are in that activity from when they are not in activity

 Activity avg sales per week of activity avg sales per week not activity lift lift % A 100+150 5+50+15 (100+150)-(5+50+15) (180)/(5+50+15) B 40+15 10 40+15- (10) (45)/(25)

So lift of activity is basically calculated as average sales per week os skus for that activity and subtracting avg sales per week of those  skus only when not in activity

Can you help me with the DAX for the same
i was able to calculate DAX for avg sales per week of activity
But am unable to calculate avg sales per week when not in activity

Thanks

2 REPLIES 2
Super User

Your expected result doesn't match your sample data. For SKU 25 the non-activity value should be 10, not 25.

Super User

Hello @vk_18,

Can you please try this:

``````AverageSalesNotInActivity =
VAR SkusInActivity =
CALCULATETABLE(
VALUES('Table'[SKUid]),
'Table'[Activity] = EARLIER('Table'[Activity])
)
VAR SkusNotInActivity =
EXCEPT(VALUES('Table'[SKUid]), SkusInActivity)
RETURN
AVERAGEX(
SUMMARIZE('Table', 'Table'[Date(Week)], "TotalSales", SUM('Table'[Sales])),
IF(COUNTROWS(INTERSECT(SkusNotInActivity, SkusInActivity)) = 0, [TotalSales], BLANK())
)``````

Should you need further assistance please don't hesitate to reach out to me.

Did I answer your question? Mark my post as a solution, this will help others!

If my response(s) assisted you in any way, don't forget to drop me a "Kudos" 🙂

Kind Regards,
Sahir Maharaj
Data Scientist | Data Engineer | Data Analyst | AI Engineer
P.S. Want me to build your Power BI solution?
➤ Lets connect on LinkedIn: Join my network of 15K+ professionals
➤ Join my free newsletter: Data Driven: From 0 to 100
➤ Website: https://sahirmaharaj.com
➤ Email: sahir@sahirmaharaj.com
➤ Want me to build your Power BI solution? Lets chat about how I can assist!
➤ Join my Medium community of 30K readers! Sharing my knowledge about data science and artificial intelligence
➤ Explore my latest project (350K+ views): Wordlit.net
➤ 100+ FREE Power BI Themes: Download Now
LinkedIn Top Voice in Artificial Intelligence, Data Science and Machine Learning