Forum Discussion

jrpoli2000's avatar
jrpoli2000
Frequent Visitor
8 years ago
Solved

Help with calculated column

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 NUMBERITEMCATEGORYSTATUS
48456558TEABEVERAGESINGLE
48456558SODABEVERAGESINGLE
48456558SODABEVERAGESINGLE
48456558JUICEBEVERAGESINGLE
48459124SODABEVERAGEMIXED
48459124JUICEBEVERAGEMIXED
48459124CANDYCONFECTIONARYMIXED
48459124CHEESEDAIRYMIXED
48459124SODABEVERAGEMIXED
48461354CHEESEDAIRYSINGLE
48461354BUTTERDAIRYSINGLE
Need Help
  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    8 years ago

    HI jrpoli2000

     

    Try this

     

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

2 Replies

  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    Community Champion
    8 years ago

    HI jrpoli2000

     

    Try this

     

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