majority
1 Topic
Calculated column returning the majority across a number of rows
Hello. I have my Purchase Line table detailing deliveries. Each delivery has it's own unique number but there can be multiple deliveries for each number (as little as 1, as many as 8). In the same table there is a Delivery Day column, so for each line there can be any of the following... Blank (not delivered) 1 (delivered on 1st day) 2 (delivered on 2nd day) 3 (delivered on 3rd day) 4 (delivered early) 5 (delivered late) I need to create a calculated column which identifies blank, 1 & 4 as 1 (or Day 1), 2 as 2 (or Day 2), 3 as 3 (or Day 3) and 5 as 5 (or Late) with the following condition - the majority eg. number of lines with a value greater than 50% gets the credit. So if there is one unique ID and Delivery Day column has five lines (deliveries), and 3 of them are Day 1 and 2 of them are Day 2, then Day 1 gets the "credit'. See example... Load No Delivery Day Desired Output L555111 1 1 L555111 1 1 L555111 1 1 L555111 1 1 L555111 1 1 L555222 2 2 L555222 2 2 L555222 3 2 L555222 3 2 L555333 1 1 L555333 0 1 L555333 0 1 L555333 0 1 L555444 1 1 L555444 1 1 L555444 1 1 L555444 2 1 L555444 2 1 In the event of a tie eg. L555222 which has two Day 2 and two Day 3 deliveries, then the earliest day (or lowest value) gets the 'credit' I have given this a go in Groups but that can't take into account differences in Delivery Days across the lines, also tried rank/topN and MAX in Measures but feel that the solution is probably either in a calculated column or a Custom Column in Query Editor, but this one is just beyond my capability in that regard. Any guidance appreciated on online resources that can help point me in the right direction to research this.Solved1.7KViews0likes6Comments