This dashboard provides information about a company’s sales in Canada. The database is in csv format. and was downloaded from the Kaggle site. All performance indicators were created with the DAX language.
Dax Code
/***************************************/
/***** Columns ******/
/***************************************/
c_Margin = Products[Unit price] - Products[Unit cost]
-----------
c_Added Value = IF(Products[Margin] > 830, "High added value", "Low added value")
-----------
c_Territory =
SWITCH(Clients[Province],
"British Columbia", "West Coast",
"Alberta", "Prairie Provinces",
"Quebec", "Central Canada",
"Ontario", "Central Canada",
"Manitoba", "Prairie Provinces",
"Saskatchewan", "Prairie Provinces",
"Nova Scotia", "Atlantic Region",
"Prince Edward Island", "Atlantic Region",
"N/A"
)
-----------
c_Quarter = "Q - " & QUARTER(Dates[Date])
-----------
c_Weekday = WEEKDAY(Dates[Date], 2)
-----------
c_WeekEnd = IF(
OR(
WEEKDAY(Dates[Date], 2) = 6,
WEEKDAY(Dates[Date], 2) = 7
),
"Yes", "No")
-----------
c_TimeDelivery = DATEDIFF(Sales[Order date], Sales[Delivery date], DAY)
-----------
c_AmountSales = Sales[Quantity] * RELATED(Products[Unit price])
-----------
c_SellingPrice = Sales[Quantity] * RELATED(Products[Unit cost])
/************/
/* Measures */
/************/
m_QtyTotal = SUM(Sales[Quantity])
-----------
m_Sales = SUM(Sales[c_AmountSales])
-----------
m_NumbTransactions = COUNTROWS(Sales)
-----------
m_#Canals = DISTINCTCOUNT(Sales[Channel])
-----------
m_MinimalQtyOrdered = MIN(Sales[Quantity])
-----------
m_MaximumQtyOrdered = Max(Sales[Quantity])
-----------
m_MarginSales = SUMX(Sales, Sales[c_AmountSales] - Sales[c_SellingPrice])
-----------
m_SalesAmount = SUMX(Sales, Sales[Quantity] * RELATED(Products[Unit price]))
-----------
m_SalesPrice = SUMX(Sales, Sales[Quantity] * RELATED(Products[Unit cost]))
-----------
m_MarginSales = [m_SalesAmount] - [m_SalesPrice]
-----------
m_AvgDaySales = AVERAGEX(Dates, [m_SalesAmount])
-----------
m_AvgClientSales = AVERAGEX(Clients, [m_SalesAmount])
-----------
m_%MarginSales = DIVIDE([m_MarginSales], [m_SalesAmount])
-----------
m_SalesAmountMontreal = SUMX(
FILTER(Clients,
Clients[City] = "Montreal"),
[m_SalesAmount]
)
-----------
m_AddFiltProduct7 = CALCULATE([m_NumbTransactions], Products[Products] = "Product 7")
-----------
m_modifFilt = CALCULATE( [m_NumbTransactions], Clients[City] = "Montreal")
-----------
m_SuprFiltClient = CALCULATE([m_NumbTransactions], ALL(Clients[Client]))
-----------
m_TransactApollo = CALCULATE([m_NumbTransactions], KEEPFILTERS(Clients[Client] = "Apollo SARL"))
-----------
m_%NumbTransactionsClient = DIVIDE([m_NumbTransactions], [m_SuprFiltClient], 0)
-----------
m_SalesAmountSPLY = CALCULATE([m_SalesAmount], SAMEPERIODLASTYEAR(Dates[Date]))
-----------
m_EcartSalesCYminusSPLY = [m_SalesAmount] - [m_SalesAmountSPLY]
-----------
m_SalesAmountSPLYdateadd = CALCULATE([m_SalesAmount], DATEADD(Dates[Date], -1, YEAR))
-----------
m_SalesAmountSPLQ = CALCULATE([m_SalesAmount], DATEADD(Dates[Date], -1, QUARTER))
-----------
m_SalesAmountEcartQuarter = [m_SalesAmount] - [m_SalesAmountSPLQ]
-----------
m_SalesAmountCumulativeYear = CALCULATE([m_SalesAmount], DATESYTD(Dates[Date]))
-----------
m_SalesAmountCumulativeQuarter = CALCULATE([m_SalesAmount], DATESQTD(Dates[Date]))
-----------
m_SalesAmountCumulativeMonth = CALCULATE([m_SalesAmount], DATESMTD(Dates[Date]))
-----------
m_SalesAmountCumulativeSPLY = CALCULATE([m_SalesAmountSPLY], DATESYTD(Dates[Date]))
-----------
m_EcartSalesCumulativeYear = [m_SalesAmountCumulativeYear] - [m_SalesAmountCumulativeSPLY]
-----------
m_MobAverageSales7days =
AVERAGEX(
DATESINPERIOD(Dates[Date], MAX(Dates[Date]), -7, DAY),
[m_SalesAmount]
)
-----------
m_ObjNumbTransactions = CALCULATE([m_NumbTransactions], SAMEPERIODLASTYEAR(Dates[Date])) * 1.5
-----------
m_MobAverageProfit1Month =
AVERAGEX(
DATESINPERIOD(Dates[Date], MAX(Dates[Date]), -1, MONTH),
[m_SalesPrice]
)
-----------
m_DeliveryTime = AVERAGE(Sales[c_TimeDelivery])
-----------
m_%MarginSalesSPLY = CALCULATE( [m_MarginSales], SAMEPERIODLASTYEAR(Dates[Date]))
/************/
/* Tables */
/************/
t_ClientsCentCanada = FILTER( Clients, Clients[c_Territory] = "Central Canada")
