topic Re: Calculated column returning the majority across a number of rows in DAX Commands and Tips

Calculated column returning the majority across a number of rows

Tob_P — Fri, 27 Jan 2023 10:43:20 GMT

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.

Re: Calculated column returning the majority across a number of rows

Tob_P — Sun, 29 Jan 2023 14:33:52 GMT

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

Re: Calculated column returning the majority across a number of rows

Tob_P — Sun, 29 Jan 2023 16:35:09 GMT

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.

Re: Calculated column returning the majority across a number of rows

v-jianboli-msft — Tue, 31 Jan 2023 05:17:25 GMT

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.

Re: Calculated column returning the majority across a number of rows

Tob_P — Tue, 31 Jan 2023 13:02:02 GMT

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?

Re: Calculated column returning the majority across a number of rows

v-jianboli-msft — Wed, 01 Feb 2023 01:22:43 GMT

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.

Re: Calculated column returning the majority across a number of rows

Tob_P — Wed, 01 Feb 2023 07:26:55 GMT

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?