Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Tob_P
Helper IV
Helper IV

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 NoDelivery DayDesired Output
L55511111
L55511111
L55511111
L55511111
L55511111
L55522222
L55522222
L55522232
L55522232
L55533311
L55533301
L55533301
L55533301
L55544411
L55544411
L55544411
L55544421
L55544421

 

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.

1 ACCEPTED SOLUTION
v-jianboli-msft
Community Support
Community Support

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:

vjianbolimsft_0-1675142234912.png

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.

View solution in original post

6 REPLIES 6
v-jianboli-msft
Community Support
Community Support

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.

@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)...

 

Tob_P_0-1675236309109.png

..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
Community Support
Community Support

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:

vjianbolimsft_0-1675142234912.png

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.

@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_0-1675170115047.png

 

Tob_P
Helper IV
Helper IV

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
Helper IV
Helper IV

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

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors