-
Notifications
You must be signed in to change notification settings - Fork 0
/
Copy pathDAX.txt
51 lines (37 loc) · 1.67 KB
/
DAX.txt
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
DAX used:
-------------------------------------------------------------------
Calendar Table:
Calendar_Table =
//Calculate Start Date
var start_date = MIN(Sales_Table[OrderDate])
//Calculate End Date
var end_date = MAX(Sales_Table[OrderDate])
RETURN
// Create a dynamic calendar table
CALENDAR ( start_date, end_date)
-------------------------------------------------------------------
-------------------------------------------------------------------
Creating new columns:
Product Margin =
var margin = CALCULATE(SUM('Sales Table'[SalesAmount]) - SUM('Sales Table'[TotalProductCost]) - SUM('Sales Table'[TaxAmt]))
RETURN
IF(margin = 0, 0, margin)
Sales Rank =
var bike_found = SEARCH("Bike", 'Product Category'[Product Category],1,0)
RETURN
IF(bike_found =1, RANK.EQ('Product Category'[Product Sales], 'Product Category'[Product Sales], DESC), -1)
Margin Percentage = DIVIDE('Product Category'[Product Margin],'Product Category'[Product Sales], 0)
Margin Rank =
var bike_found = SEARCH("Bike", 'Product Category'[Product Category],1,0)
RETURN
IF(bike_found =1, RANK.EQ('Product Category'[Margin Percentage], 'Product Category'[Margin Percentage], DESC), -1)
Cumulative Rank =
IF(OR('Product Category'[Sales Rank] = -1, 'Product Category'[Margin Rank] = -1) = FALSE, 'Product Category'[Sales Rank] * 'Product Category'[Margin Rank], -1)
Sales Contributor Category =
SWITCH(TRUE(),
'Product Category'[Sales Rank] = -1, "NA",
'Product Category'[Sales Rank] <= 25, "Top Contributor",
'Product Category'[Sales Rank] >= 75, "Bottom Contributor",
"Middle Contributor"
)
-------------------------------------------------------------------