Forum Discussion
Quartile calculation with multiple conditions
Super UserIt would be something like this
Hours Quartile =
SWITCH(TRUE(),
sum('Table'[Sum of Hours])<calculate(PERCENTILE.INC('Table'[Sum of Hours],0.25),ALLSELECTED('Table')),1,
sum('Table'[Sum of Hours])<calculate(PERCENTILE.INC('Table'[Sum of Hours],0.5),ALLSELECTED('Table')),2,
sum('Table'[Sum of Hours])<calculate(PERCENTILE.INC('Table'[Sum of Hours],0.75),ALLSELECTED('Table')),3,
4)
Thanks Ibendlin. I am still having an issue with the percentile pieces:
calculate(PERCENTILE.INC('Table'[Sum of Hours],0.25),ALLSELECTED('Table'))The table that I am referencing has many line items that add up to the total 'sum of hours'. For instance, the max in the Hours column is 375. The line above appears to be looking at those individual line items to calculate the quartile vs the summed amount per a 'Unit Number'.
- lbendlin
Please provide sanitized sample data that fully covers your issue.
Please show the expected outcome based on the sample data you provided.I am trying to produce a summarized table similar to the below. The hours are the sum of the hours from the data table. The Quartile is based on the hours for the line of business and category.
H
I
J
K
L
M
Truck
Region
Line of Business
Category
Hours
Quartile
4
2
Central
Fuel
Tractor
1,317.81
1
5
3
Central
Operations
Truck
1,347.47
1
6
4
Central
Fuel
Tractor
1,627.94
2
7
5
Central
Fuel
Tractor
765.80
1
8
6
Central
Fuel
Tractor
1,691.58
3
9
7
Central
Fuel
Tractor
584.35
1
10
8
Central
Operations
Truck
2,179.61
4
11
9
Central
Fuel
Tractor
1,351.00
2
12
10
Central
Fuel
Tractor
144,413.99
4
13
12
Central
Fuel
Tractor
1,711.71
4
14
13
Central
Fuel
Tractor
1,639.55
3
The formula in M4 is: IF(L4<=INDEX($T$5:$W$6,MATCH(Q4,$S$5:$S$6,0),1),1,IF(L4<=INDEX($T$5:$W$6,MATCH(Q4,$S$5:$S$6,0),2),2,IF(L4<=INDEX($T$5:$W$6,MATCH(Q4,$S$5:$S$6,0),3),3,4)))
Here is a sample dataset that is used to populate the Summary table, above.
B
C
D
E
F
Truck
Region
Line of Business
Category
Hours
4
2
Central
Fuel
Tractor
500.50
5
10
Central
Fuel
Tractor
68,005.00
6
6
Central
Fuel
Tractor
0.00
7
3
Central
Operations
Truck
852.60
8
8
Central
Operations
Truck
765.00
9
7
Central
Fuel
Tractor
135.60
10
5
Central
Fuel
Tractor
653.40
11
4
Central
Fuel
Tractor
398.63
12
12
Central
Fuel
Tractor
403.80
13
9
Central
Fuel
Tractor
345.00
14
13
Central
Fuel
Tractor
800.60
15
2
Central
Fuel
Tractor
653.00
16
10
Central
Fuel
Tractor
29,432.00
17
6
Central
Fuel
Tractor
698.00
18
3
Central
Operations
Truck
135.70
19
8
Central
Operations
Truck
657.00
20
7
Central
Fuel
Tractor
263.10
21
5
Central
Fuel
Tractor
86.90
22
4
Central
Fuel
Tractor
635.14
23
12
Central
Fuel
Tractor
742.50
24
9
Central
Fuel
Tractor
654.10
25
13
Central
Fuel
Tractor
498.60
26
2
Central
Fuel
Tractor
164.31
27
10
Central
Fuel
Tractor
46,976.99
28
6
Central
Fuel
Tractor
993.58
29
3
Central
Operations
Truck
359.17
30
8
Central
Operations
Truck
757.61
31
7
Central
Fuel
Tractor
185.65
32
5
Central
Fuel
Tractor
25.50
33
4
Central
Fuel
Tractor
594.17
34
12
Central
Fuel
Tractor
565.41
35
9
Central
Fuel
Tractor
351.90
36
13
Central
Fuel
Tractor
340.35
To get the summarized table in Excel, I used a Helper column (concatenate Line of Business & Category).
Q
Helper
4
FuelTractor
5
OperationsTruck
6
FuelTractor
7
FuelTractor
8
FuelTractor
9
FuelTractor
10
OperationsTruck
11
FuelTractor
12
FuelTractor
13
FuelTractor
14
FuelTractor
The formula in Q4 is: J4&K4
Part 2:
To calculate the quartile in Excel, I produced a reference table:
S T U V W 4 Quartile 1 1.00 2.00 3.00 4.00 5 FuelTractor 1,317.81 1,627.94 1,691.58 144,413.99 6 OperationsTruck 1,555.51 1,763.54 1,971.58 2,179.61 The formula in T5 is: QUARTILE.INC(IF($Q$4:$Q$14=$S5,$L$4:$L$14),T$4)
Based on this sample data, I believe the dax formula is generating a Quartile table like this:
S T U V W 9 Quartile2 1.00 2.00 3.00 4.00 10 340.35 565.41 742.50 68,005.00
The formula in T10 is: QUARTILE.INC($F$4:$F$36,T$9)
And this is producing the Quartiles shown in Quartile 2:H I J K L M O Truck Region Line of Business Category Hours Quartile Quartile2 4 2 Central Fuel Tractor 1,317.81 1 4 5 3 Central Operations Truck 1,347.47 1 4 6 4 Central Fuel Tractor 1,627.94 2 4 7 5 Central Fuel Tractor 765.80 1 4 8 6 Central Fuel Tractor 1,691.58 3 4 9 7 Central Fuel Tractor 584.35 1 3 10 8 Central Operations Truck 2,179.61 4 4 11 9 Central Fuel Tractor 1,351.00 2 4 12 10 Central Fuel Tractor 144,413.99 4 4 13 12 Central Fuel Tractor 1,711.71 4 4 14 13 Central Fuel Tractor 1,639.55 3 4
The difference in the quartile calcs is using the summed hours in the summary table vs the individual line items in the dataset.