1 #74BDCB --Total Profit
2 #E57F84 --Total Revenue
3 #B7AC44 --Total Quantity
% Change Measure =
VAR vCurrentValue = [Main_Graph_Country]
VAR vPreviousValue =
SWITCH(
TRUE(),
ISINSCOPE(Dates[Date].[Day]), CALCULATE([Main_Graph_Country], DATEADD(Dates[Date], -1,DAY)),
ISINSCOPE(Dates[Date].[Month]), CALCULATE([Main_Graph_Country], DATEADD(Dates[Date], -1,MONTH)),
ISINSCOPE(Dates[Date].[Quarter]), CALCULATE([Main_Graph_Country], DATEADD(Dates[Date], -1,QUARTER)),
ISINSCOPE(Dates[Date].[Year]), CALCULATE([Main_Graph_Country], DATEADD(Dates[Date], -1,YEAR))
)
RETURN
DIVIDE(vCurrentValue - vPreviousValue, vPreviousValue)
-----------------------------------------------------------------------------------------------
Graph_AVG_Delivery_time =
VAR vMonth = [Delivery Time Avg]
VAR vQuarter =
CALCULATE(
[Delivery Time Avg],
ALL(),
VALUES(Dates[Year]),
VALUES(Dates[Months])
)
VAR vYear =
CALCULATE(
[Delivery Time Avg],
ALL(),
VALUES(Dates[Year]),
VALUES(Dates[Quarter])
)
RETURN
SWITCH(TRUE(),
ISFILTERED(Dates[Month Name (long)]) && ISFILTERED(Dates[Year]), vMonth,
ISFILTERED(Dates[Quarter]) && ISFILTERED(Dates[Year]), vQuarter,
ISFILTERED(Dates[Date]), vYear
)
-----------------------------------------------------------------------------------------------
Graph_AVG_Profit_by_Transaction =
VAR vMonth = [Avg Profit by Transaction]
VAR vQuarter =
CALCULATE(
[Avg Profit by Transaction],
ALL(),
VALUES(Dates[Year]),
VALUES(Dates[Months])
)
VAR vYear =
CALCULATE(
[Avg Profit by Transaction],
ALL(),
VALUES(Dates[Year]),
VALUES(Dates[Quarter])
)
RETURN
SWITCH(TRUE(),
ISFILTERED(Dates[Month Name (long)]) && ISFILTERED(Dates[Year]), vMonth,
ISFILTERED(Dates[Quarter]) && ISFILTERED(Dates[Year]), vQuarter,
ISFILTERED(Dates[Date]), vYear
)
-----------------------------------------------------------------------------------------------
Graph_Margin_Profit =
VAR vMonth = [Margin Profit]
VAR vQuarter =
CALCULATE(
[Margin Profit],
ALL(),
VALUES(Dates[Year]),
VALUES(Dates[Months])
)
VAR vYear =
CALCULATE(
[Margin Profit],
ALL(),
VALUES(Dates[Year]),
VALUES(Dates[Quarter])
)
RETURN
SWITCH(TRUE(),
ISFILTERED(Dates[Month Name (long)]) && ISFILTERED(Dates[Year]), vMonth,
ISFILTERED(Dates[Quarter]) && ISFILTERED(Dates[Year]), vQuarter,
ISFILTERED(Dates[Date]), vYear
)
-----------------------------------------------------------------------------------------------
Graph_Total_Cost =
VAR vMonth = [Total Cost]
VAR vQuarter =
CALCULATE(
[Total Cost],
ALL(),
VALUES(Dates[Year]),
VALUES(Dates[Months])
)
VAR vYear =
CALCULATE(
[Total Cost],
ALL(),
VALUES(Dates[Year]),
VALUES(Dates[Quarter])
)
RETURN
SWITCH(TRUE(),
ISFILTERED(Dates[Month Name (long)]) && ISFILTERED(Dates[Year]), vMonth,
ISFILTERED(Dates[Quarter]) && ISFILTERED(Dates[Year]), vQuarter,
ISFILTERED(Dates[Date]), vYear
)
-----------------------------------------------------------------------------------------------
Graph_Total_Profit =
VAR vMonth = [Total Profit]
VAR vQuarter =
CALCULATE(
[Total Profit],
ALL(),
VALUES(Dates[Year]),
VALUES(Dates[Months])
)
VAR vYear =
CALCULATE(
[Total Profit],
ALL(),
VALUES(Dates[Year]),
VALUES(Dates[Quarter])
)
RETURN
SWITCH(TRUE(),
ISFILTERED(Dates[Month Name (long)]) && ISFILTERED(Dates[Year]), vMonth,
ISFILTERED(Dates[Quarter]) && ISFILTERED(Dates[Year]), vQuarter,
ISFILTERED(Dates[Date]), vYear
)
-----------------------------------------------------------------------------------------------
Graph_Total_Quantity =
VAR vMonth = [Total Quantity]
VAR vQuarter =
CALCULATE(
[Total Quantity],
ALL(),
VALUES(Dates[Year]),
VALUES(Dates[Months])
)
VAR vYear =
CALCULATE(
[Total Quantity],
ALL(),
VALUES(Dates[Year]),
VALUES(Dates[Quarter])
)
RETURN
SWITCH(TRUE(),
ISFILTERED(Dates[Month Name (long)]) && ISFILTERED(Dates[Year]), vMonth,
ISFILTERED(Dates[Quarter]) && ISFILTERED(Dates[Year]), vQuarter,
ISFILTERED(Dates[Date]), vYear
)
-----------------------------------------------------------------------------------------------
Graph_Total_Revenue =
VAR vMonth = [Total Revenue]
VAR vQuarter =
CALCULATE(
[Total Revenue],
ALL(),
VALUES(Dates[Year]),
VALUES(Dates[Months])
)
VAR vYear =
CALCULATE(
[Total Revenue],
ALL(),
VALUES(Dates[Year]),
VALUES(Dates[Quarter])
)
RETURN
SWITCH(TRUE(),
ISFILTERED(Dates[Month Name (long)]) && ISFILTERED(Dates[Year]), vMonth,
ISFILTERED(Dates[Quarter]) && ISFILTERED(Dates[Year]), vQuarter,
ISFILTERED(Dates[Date]), vYear
)
-----------------------------------------------------------------------------------------------
Graph_Total_Transactions =
VAR vMonth = [Total Transactions]
VAR vQuarter =
CALCULATE(
[Total Transactions],
ALL(),
VALUES(Dates[Year]),
VALUES(Dates[Months])
)
VAR vYear =
CALCULATE(
[Total Transactions],
ALL(),
VALUES(Dates[Year]),
VALUES(Dates[Quarter])
)
RETURN
SWITCH(TRUE(),
ISFILTERED(Dates[Month Name (long)]) && ISFILTERED(Dates[Year]), vMonth,
ISFILTERED(Dates[Quarter]) && ISFILTERED(Dates[Year]), vQuarter,
ISFILTERED(Dates[Date]), vYear
)
-----------------------------------------------------------------------------------------------
Main_Graph_Country =
SWITCH([My_Slicer],
1, [Total Profit],
2, [Total Revenue],
3, [Total Quantity]
)
-----------------------------------------------------------------------------------------------
Rank_Clients_Graph =
VAR vTop = 5
VAR vTop5Profit = RANKX(ALL(Clients),[Total Profit],,DESC) --1
VAR vBot5Profit = RANKX(ALL(Clients),[Total Profit],,ASC) --2
VAR vTop5Revenue = RANKX(ALL(Clients),[Total Revenue],,DESC) --3
VAR vBot5Revenue = RANKX(ALL(Clients),[Total Revenue],,ASC) --4
VAR vTop5Quantity = RANKX(ALL(Clients),[Total Quantity],,DESC) --5
VAR vBot5Quantity = RANKX(ALL(Clients),[Total Quantity],,ASC) --6
RETURN
SWITCH(
Slicers[My_SlicerTop],
1, IF(vTop5Profit <= vTop, vTop5Profit, BLANK()),
2, IF(vBot5Profit <= vTop, vBot5Profit, BLANK()),
3, IF(vTop5Revenue <= vTop, vTop5Revenue, BLANK()),
4, IF(vBot5Revenue <= vTop, vBot5Revenue, BLANK()),
5, IF(vTop5Quantity <= vTop, vTop5Quantity, BLANK()),
6, IF(vBot5Quantity <= vTop, vBot5Quantity, BLANK())
)
-----------------------------------------------------------------------------------------------
Avg Profit by Transaction =
AVERAGEX(
VALUES(Sales[Order number]),
[Total Profit]
)
-----------------------------------------------------------------------------------------------
AVG. Sales per Day =
AVERAGEX(VALUES(Dates[Date]), [Total Revenue])
-----------------------------------------------------------------------------------------------
Best Selling Day =
MAXX(
TOPN(1,
SUMMARIZE(
Dates,
Dates[Wekk Name],
"Selling", [AVG. Sales per Day]),
[AVG. Sales per Day]),
Dates[Wekk Name]
)
-----------------------------------------------------------------------------------------------
Delivery Time Avg = AVERAGE(Sales[Delivery Time])
-----------------------------------------------------------------------------------------------
Margin Profit = DIVIDE([Total Profit], [Total Revenue], 0)
-----------------------------------------------------------------------------------------------
Top Customer Name =
MAXX(
TOPN(1,
SUMMARIZE(Clients,Clients[Client], "Sales", [Total Revenue]),
[Sales],DESC),
Clients[Client])
-----------------------------------------------------------------------------------------------
Total Cost = SUMX(Sales, Sales[Quantity] * RELATED(Products[Unit cost]))
-----------------------------------------------------------------------------------------------
Total Profit =
VAR vTotalRevenue = SUMX(Sales, Sales[Quantity] * RELATED(Products[Unit price]))
VAR vTotalCost = SUMX(Sales, Sales[Quantity] * RELATED(Products[Unit cost]))
VAR vTotalProfit = vTotalRevenue - vTotalCost
RETURN
vTotalProfit
-----------------------------------------------------------------------------------------------
Total Quantity = SUM(Sales[Quantity])
-----------------------------------------------------------------------------------------------
Total Revenue = SUMX(Sales, Sales[Quantity] * RELATED(Products[Unit price]))
-----------------------------------------------------------------------------------------------
Total Transactions = COUNT(Sales[Order number])
-----------------------------------------------------------------------------------------------
F Value = DISTINCTCOUNT(Sales[Order number])
-----------------------------------------------------------------------------------------------
Last Transaction =
MAXX(
FILTER(
Sales,
Sales[Id Client] = Sales[Id Client]),
Sales[Order date]
)
-----------------------------------------------------------------------------------------------
M Value =
VAR vTotalRevenue = SUMX(Sales, Sales[Quantity] * RELATED(Products[Unit price]))
VAR vTotalTransactions = COUNT(Sales[Id Client])
RETURN
DIVIDE(vTotalRevenue, vTotalTransactions, 0)
-----------------------------------------------------------------------------------------------
R Value = DATEDIFF([Last Transaction], TODAY(), DAY)
-----------------------------------------------------------------------------------------------
My_Slicer =
SWITCH(TRUE(),
SELECTEDVALUE('My measure'[Measure]) = "Total Profit", 1,
SELECTEDVALUE('My measure'[Measure]) = "Total Revenue", 2,
SELECTEDVALUE('My measure'[Measure]) = "Total Quantity", 3
)
-----------------------------------------------------------------------------------------------
My_SlicerTop =
SWITCH(TRUE,
SELECTEDVALUE('My measure'[Measure]) = "Total Profit" && SELECTEDVALUE('My Top 5'[Top]) = "Top 5", 1,
SELECTEDVALUE('My measure'[Measure]) = "Total Profit" && SELECTEDVALUE('My Top 5'[Top]) = "Bottom 5", 2,
SELECTEDVALUE('My measure'[Measure]) = "Total Revenue" && SELECTEDVALUE('My Top 5'[Top]) = "Top 5", 3,
SELECTEDVALUE('My measure'[Measure]) = "Total Revenue" && SELECTEDVALUE('My Top 5'[Top]) = "Bottom 5", 4,
SELECTEDVALUE('My measure'[Measure]) = "Total Quantity" && SELECTEDVALUE('My Top 5'[Top]) = "Top 5", 5,
SELECTEDVALUE('My measure'[Measure]) = "Total Quantity" && SELECTEDVALUE('My Top 5'[Top]) = "Bottom 5", 6
)
-----------------------------------------------------------------------------------------------
My_SlicerYoY =
SWITCH(TRUE(),
SELECTEDVALUE('% Change_Graph'[%_Change]) = "DoD", 1,
SELECTEDVALUE('% Change_Graph'[%_Change]) = "MoM", 2,
SELECTEDVALUE('% Change_Graph'[%_Change]) = "QoQ", 3,
SELECTEDVALUE('% Change_Graph'[%_Change]) = "YoY", 4
)
-----------------------------------------------------------------------------------------------
Graph_Country_Title = "How is our " & SELECTEDVALUE('My measure'[Measure]) & " distributed geographically ?"
-----------------------------------------------------------------------------------------------
Graph_Top_Title = "What are my " & SELECTEDVALUE('My Top 5'[Top]) & " clients by " & SELECTEDVALUE('My measure'[Measure]) & " ?"
-----------------------------------------------------------------------------------------------
Tool_Tips = SELECTEDVALUE('My measure'[Measure])
-----------------------------------------------------------------------------------------------
Dates = CALENDAR(MIN(Sales[Order date]), MAX(Sales[Order date]))
-----------------------------------------------------------------------------------------------
Year = YEAR(Dates[Date])
-----------------------------------------------------------------------------------------------
Quarter = "Q-" & QUARTER(Dates[Date])
-----------------------------------------------------------------------------------------------
Months = MONTH(Dates[Date])
-----------------------------------------------------------------------------------------------
Month Name (long) = FORMAT(Dates[Date], "mmmm")
-----------------------------------------------------------------------------------------------
Month Name (Short) = FORMAT(Dates[Date], "mmm")
-----------------------------------------------------------------------------------------------
Week numb = WEEKNUM(Dates[Date],1)
-----------------------------------------------------------------------------------------------
Week = WEEKDAY(Dates[Date], 2)
-----------------------------------------------------------------------------------------------
Wekk Name = FORMAT(Dates[Date], "dddd")
-----------------------------------------------------------------------------------------------
Period = FORMAT(Dates[Date], "MMM-YYYY")
-----------------------------------------------------------------------------------------------
R Score =
SWITCH(
TRUE(),
'RFM Table'[R Value] <= PERCENTILE.EXC('RFM Table'[R Value], 0.20), "5",
'RFM Table'[R Value] <= PERCENTILE.EXC('RFM Table'[R Value], 0.40), "4",
'RFM Table'[R Value] <= PERCENTILE.EXC('RFM Table'[R Value], 0.60), "3",
'RFM Table'[R Value] <= PERCENTILE.EXC('RFM Table'[R Value], 0.80), "2",
"1"
)
-----------------------------------------------------------------------------------------------
F Score =
SWITCH(
TRUE(),
'RFM Table'[F Value] <= PERCENTILE.EXC('RFM Table'[F Value], 0.20), "1",
'RFM Table'[F Value] <= PERCENTILE.EXC('RFM Table'[F Value], 0.40), "2",
'RFM Table'[F Value] <= PERCENTILE.EXC('RFM Table'[F Value], 0.60), "3",
'RFM Table'[F Value] <= PERCENTILE.EXC('RFM Table'[F Value], 0.80), "4",
"5"
)
-----------------------------------------------------------------------------------------------
M Score =
SWITCH(
TRUE(),
'RFM Table'[M Value] <= PERCENTILE.EXC('RFM Table'[M Value], 0.20), "1",
'RFM Table'[M Value] <= PERCENTILE.EXC('RFM Table'[M Value], 0.40), "2",
'RFM Table'[M Value] <= PERCENTILE.EXC('RFM Table'[M Value], 0.60), "3",
'RFM Table'[M Value] <= PERCENTILE.EXC('RFM Table'[M Value], 0.80), "4",
"5"
)
-----------------------------------------------------------------------------------------------
RFM = 'RFM Table'[R Score] & 'RFM Table'[F Score] & 'RFM Table'[M Score]
-----------------------------------------------------------------------------------------------
Recency =
SWITCH(
'RFM Table'[R Score],
5, "Excellent",
4, "Very good",
3, "Good",
2, "Fair",
1, "Poor")
-----------------------------------------------------------------------------------------------
Time Since Last Order =
QUOTIENT('RFM Score'[R Value], 365) & " year " & QUOTIENT(MOD('RFM Table'[R Value], 365), 30) & " months "
& MOD(MOD('RFM Table'[R Value], 365), 30) & " days"
-----------------------------------------------------------------------------------------------
Delivery Time = DATEDIFF(Sales[Order date], Sales[Delivery date], DAY)
-----------------------------------------------------------------------------------------------
