Forum Discussion
Quartile calculation with multiple conditions
Super UserPlease 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.