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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
vk_18
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

 

SKUidActivityDate(Week)Sales

10

A22-02-2022150
20A22-02-2022100
15B

15-10-

2022

40
10no2-08-20225
25B15-10-202215
20no2-08-202250
25no15-01-202210
10no6-05-202215

 

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

Activityavg sales per week of activityavg sales per week not activityliftlift %
A100+1505+50+15(100+150)-(5+50+15)(180)/(5+50+15)
B40+151040+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
lbendlin
Super User
Super User

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

Sahir_Maharaj
Super User
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 13K+ 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

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

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

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.