Quartile calculation with multiple conditions

[jjmartinso]

Hello All, 

I have searched for the solution on this, and I have tried multiple things. However, I have not been successful.  I am trying to calculate the quartile for Hours and Quantity in the below table.  The first five columns of the below snapshot is coming from the Asset table, Hours is coming from the Activity table, and Quantity is coming from the Delivery table.  Additionally, the visual is filtered by date (last 3 calendar months).

Condition: The assets should be evaluated on the Line of Bus and Category.  For example, for the first row, I am trying to see what quartile the 171 hours falls into based on the Operations Line of Bus and the Pickup Category.  For the last line, I am trying to see what quartile the 1,539 hours falls into and what quartile the quantity of 3,901,873 falls into based on Fuel Line of Bus and the Tractor Category 

My Power BI report currently contains columns A through I.  Columns J - K are not included and rows after 14 are not included.  The Hours Quart and Quant Quart, column H & I, is where I need assistance.

1	A	B	C	D	E	F	G	H	I	J	K
2	Unit Number	Region	Line of Business	Unit Class	Category	Sum of Hours	Sum of Quantity	Hours Quart	Quant Quart		Helper
3	MTR2023802	Central	Fuel	Power Unit	Tractor	1317.81	227601	1	4		FuelTractor
4	MTR2023810	Central	Fuel	Power Unit	Tractor	1789.83	144413.99	4	4		FuelTractor
5	MTR2023806	Central	Fuel	Power Unit	Tractor	1691.58	143548	3	4		FuelTractor
6	MTR2023803	Central	Operations	Power Unit	Truck	1347.47	140605	1	4		OperationsTruck
7	MTR2023808	Central	Operations	Power Unit	Truck	2179.61	121130	1	1		OperationsTruck
9	MTR2023807	Central	Fuel	Power Unit	Tractor	584.35	100399	1	4		FuelTractor
10	MTR2084703	Central	Fuel	Power Unit	Tractor	765.8	97902	1	4		FuelTractor
11	MTR2084704	Central	Fuel	Power Unit	Tractor	1627.94	93272	2	4		FuelTractor
12	MTR2023812	Central	Fuel	Power Unit	Tractor	1711.71	65698	4	4		FuelTractor
13	MTR2023809	Central	Fuel	Power Unit	Tractor	1351	51320	2	4		FuelTractor
14	MTR2023813	Central	Fuel	Power Unit	Tractor	1639.55	14812	3	4		FuelTractor
15
16			Unique	1	2	3	4
17			FuelTractor	1317.81	1627.94	1691.58	1789.83
18			OperationsTruck	125998.75	130868	135736.25	140605

On the above sample table from Excel, I used a helper column (cell K3 formula =D3&F3). 

The table below the data, starting on row 16, is the quartile table that I used for my Hours Quart and Quant Quart formulas.  This is only to help calculate the formulas in columns H & I

D17 formula is: QUARTILE.INC(IF($L$3:$L$13=$D16,$G$3:$G$13),E$15)

The formula used for Column H is:

IF(G3<=INDEX($E$16:$H$17,MATCH($L3,$D$16:$D$17,0),1),1,IF(G3<=INDEX($E$16:$H$17,MATCH($L3,$D$16:$D$17,0),2),2,IF(G3<=INDEX($E$16:$H$17,MATCH($L3,$D$16:$D$17,0),3),3,4)))

One additional aspect, the table in Power BI also includes filters for Region, Line of Business, and Category, shown in the below.  I am not sure if this would need to be incorporated in a dax formula.

I would greatly appreciate any guidance or assistance to incorporate this logic in Power BI.  Thanks!

[lbendlin]

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-...
Please show the expected outcome based on the sample data you provided.

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

[jjmartinso]

@lbendlin , I have modified the original post to include an Excel example.  I hope that provides enough detail.  If not, let me know.

[lbendlin]

It 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)

[jjmartinso]

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.

[jjmartinso]

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

[jjmartinso]

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.