Help with calculated column

[jrpoli2000]

I got an invoice table listing all its items, I need a calculated column to identify whether the invoice contains only a SINGLE or MIXED category based on the category column. Below is the sample and the desired output for the status column.

Thanks for all the help.

INVOICE NUMBER	ITEM	CATEGORY	STATUS
48456558	TEA	BEVERAGE	SINGLE
48456558	SODA	BEVERAGE	SINGLE
48456558	SODA	BEVERAGE	SINGLE
48456558	JUICE	BEVERAGE	SINGLE
48459124	SODA	BEVERAGE	MIXED
48459124	JUICE	BEVERAGE	MIXED
48459124	CANDY	CONFECTIONARY	MIXED
48459124	CHEESE	DAIRY	MIXED
48459124	SODA	BEVERAGE	MIXED
48461354	CHEESE	DAIRY	SINGLE
48461354	BUTTER	DAIRY	SINGLE

[Zubair_Muhammad · Accepted solution]

HI @jrpoli2000

Try this

Status =
IF (
    CALCULATE (
        DISTINCTCOUNT ( Table1[CATEGORY] ),
        ALLEXCEPT ( Table1, Table1[INVOICE NUMBER] )
    )
        > 1,
    "MIXED",
    "SINGLE"
)

[jrpoli2000]

Thanks again!!!