Calculated column returning the majority across a number of rows

[Tob_P]

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.

[v-jianboli-msft]

Hi @Tob_P ,

What do you mean by Grouping and can you describe your detailed steps?

I can't tell where the problem is just by the description of "Grouping".

Best Regards,

Jianbo Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Tob_P]

@v-jianboli-msft 

I used groupings in the Data view based in your calculated column (I had to replace 5 with 4 in your DAX)...

..then applied a measure giving the count of the rows in the table which gave the output of everything in Day 1. Is it the case that this calculated column cannot be grouped?

[v-jianboli-msft · Accepted solution]

Hi @Tob_P ,

Please try:

Column =
VAR _a =
    COUNTROWS ( FILTER ( 'Table', [Load No] = EARLIER ( 'Table'[Load No] ) ) )
VAR _b =
    SUMMARIZE (
        FILTER ( 'Table', [Load No] = EARLIER ( 'Table'[Load No] ) ),
        'Table'[Delivery Day],
        "Day", IF ( [Delivery Day] IN { 0, 1, 4 }, 1, [Delivery Day] ),
        "Count", COUNT ( 'Table'[Delivery Day] )
    )
VAR _c =
    SUMMARIZE (
        _b,
        [Day],
        [Count],
        "Flag",
            SWITCH (
                TRUE (),
                SUMX ( _b, [Count] ) > ( 0.5 * _a ), 1,
                SUMX ( _b, [Count] ) = ( 0.5 * _a )
                    && [Day] = MINX ( _b, [Day] ), 1
            )
    )
RETURN
    MINX ( FILTER ( _c, [Flag] = 1 ), [Day] )

Final output:

Best Regards,

Jianbo Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Tob_P]

@v-jianboli-msftThank you for your response - it's really appreciated.

I think that this will give me what I want, but perhaps you can tell me why it would not work if I used Groupings?

[Tob_P]

Perhaps a simpler way of me expressing what I'm looking for is..

If the count of the value in Delivery Day is greater than 50% or .5, then return that value for each Delivery Number.

[Tob_P]

Bumping this up to see if anyone able to help - two days of working away on it myself with no real headway! Appreciate any guidance. Thanks