Forum Discussion
Quartile calculation with multiple conditions
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!
7 Replies
- lbendlin
Super User
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-p/963216
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/1447523lbendlin , 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)
