POWER BI PROJECT - sales and acquisition

#powerbi #ba

Ritwik Nautiyal Jan 08 2022 · 1 min read
Share this

Step-1: Load the data.

Note: This is done by checking for the null or duplicated values.

Step-2: Creating the DAX Commands for the following

a.       Total Revenue = SUMX('Sales',Sales[Order Quantity]*Sales[Unit Price])

b.       Order Quantity = SUMX(Sales,Sales[Order Quantity])

c.       Net Profit = [Total Sales] - [Total Cost]

d.       Profit Margin = DIVIDE([Profit], [Total Sales], 0)

e.       Total Cost = sumx(sales, [Total Quantity]*Sales[Total Unit Cost])

Step-3: Performance LY for any selected Year; Comparing the Performance vs LY; Sales 2 years ago for any Selected Year; Moving Average in terms of Profit & Sales

DAX Commands are as follows:

a.       Sales Last Year = CALCULATE([Total Sales], SAMEPERIODLASTYEAR(‘Calender’[Date]))

b.       Profit Last Year = CALCULATE([Profit],SAMEPERIODLASTYEAR(‘Calender’[Date]))

c. Sales 2 Years Ago = CALCULATE([Total sales],DATEADD(‘Calender’[Date],-2,YEAR))

d.       Moving Average for Sales =

AVERAGEX(FILTER(ALLSELECTED(Date),’Calender’[Date]<=MAX(‘Calender’[Date])),[Total Sales])

e.       Profit Moving Average =

AVERAGEX(FILTER(ALLSELECTED(Date), ’Calender’ [Date]<=MAX(’Calender’[Date])),[Profit])

STEP-4: Quarter-wise analysis

Quarterly sales=CALCULATE([Total sales],DATEADD('date table'[Date],-1,QUARTER))

STEP-5: Total Sales, Profit and % Profit

STEP-6: Sales Comparison between Cumulative Performance vs Cumulative Performance LY using a area chart

DAX Commands are as follows:

a.       Cumulative Sales = CALCULATE([Total Sales], FILTER(ALLSELECTED('Date Table'),'Date Table'[Date] <= MAX('Date Table'[Date])))

b.       Cumulative Sales Last Year = CALCULATE([Sales Last Year],FILTER(ALLSELECTED('Date Table'[Date]),'Date Table'[Date] <= MAX('Date Table'[Date])))

STEP-7: Top Performer in terms of Product and Customer

DAX Commands are as follows:

a.       Top 7 Products = CALCULATE([Total Sales],FILTER(VALUES('Product Data'[Product Name]),IF(RANKX(ALL('Product Data'[Product Name]),[Total Sales],,DESC)<=7,[Total Sales],BLANK())))

b. Top 7 Customers = CALCULATE([Total Sales],FILTER(VALUES('Customer Data'[Customer Names]),IF(RANKX(ALL('Customer Data'[Customer Names]),[Total Sales],,DESC)<=7,[Total Sales],BLANK())))

STEP-8: Performance in terms of Region Sales

Read next