Create flags for specific criteria where there are duplicate values in the same column

[murphm6]

Hi everyone,

I'm looking to create a series of flags for invoice numbers (in my case, want to flag the 'Short Invoice' column, however, as you notice, there are multiple of the same values in the 'Short Invoice' column. 

In this situation, I want to flag any 'Short Invoice" where 'Ocean Freight' is less than 75% of the 'INVOICE TOTAL'. How can I complete this analysis by looking at each specific 'Short Invoice' and it's associated charge types? I feel like I should summarize the table, but then I remove the different charge types, which is the focus of this analysis. 

Thanks in advance!

[v-jianboli-msft · Accepted solution]

Hi @murphm6 ,

Please try:

Flag = 
IF (
    'Table'[Short Invoice]
        = MAXX (
            FILTER ( 'Table', [Short Invoice] = EARLIER ( 'Table'[Short Invoice])),
        [Short Invoice])
        && SUMX (
            FILTER (
                'Table',
                [Short Invoice] = EARLIER ( 'Table'[Short Invoice] )
                    && [Charge Type] = "OCEAN FREIGHT"
            ),
            [Cost]
        ) < 0.75 * [INVOICE TOTAL],
    1,0
)

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.

[v-jianboli-msft]

Hi @murphm6 ,

What is your "Short invoice" ? Does it refer to "Invoice Number"? And what will the flag display if  'Ocean Freight' is greater than 75% of the 'INVOICE TOTAL'?

Based on my comprehension, I have created a simple sample:

Please try:

Flag =
IF (
    'Table'[Invoice Number]
        = MAXX (
            FILTER ( 'Table', [Invoice Number] = EARLIER ( 'Table'[Invoice Number] ) ),
            [Invoice Number]
        )
        && SUMX (
            FILTER (
                'Table',
                [Invoice Number] = EARLIER ( 'Table'[Invoice Number] )
                    && [Charge Type] = "OCEAN FREIGHT"
            ),
            [Cost]
        ) < 0.75 * [INVOICE TOTAL],
    [Invoice Number]
)

 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.

[murphm6]

Yes you're correct, the 'Short Invoice' is 'Invoice Number' minus the last digit. 

Ideally, i'd like to create a series of flags and the flag would just be a simple 1 or 0. So, if for a certain 'Short Invoice', the Ocean freight is less than 75% of the total, I'd like this new flag column to just show a '1', so I can filter by that '1' and look at all 'Short Invoices' which have this issue

The flag should be flagging orders at the 'Short Invoice' level. In the earlier screenshot, I just filtered to one example, but here's a snip of what the unfiltered data looks like