Forum Discussion

nschmidt's avatar
nschmidt
Frequent Visitor
5 years ago
Solved

Count with Allexcept

I need to count part numbers that were sold on more than one time by item type. I have 2 tables and example data below. I need to count the number of invoices by part #, then count the number of part #'s that have more than 1 and break that out by item type.

 

STD = 2

EXPAN = 3

 

 

  • AlB's avatar
    AlB
    5 years ago

    Hi nschmidt 

    1. Create a 1-to-many relationship between 'Product'[Part#] and  Sales[Part#]

    2. Place 'Product'[Item type] in the rows of a table visual

    3. Create this measure and place it in the visual

    Measure =
COUNTROWS (
    FILTER ( DISTINCT ( 'Product'[Part #] ), CALCULATE ( COUNTROWS ( Sales ) ) > 1 )
)

    Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

    Contact me privately for support with any larger-scale BI needs, tutoring, etc.

    Cheers 

1 Reply

  • AlB's avatar
    AlB
    Icon for Community Champion rankCommunity Champion
    5 years ago

    Hi nschmidt 

    1. Create a 1-to-many relationship between 'Product'[Part#] and  Sales[Part#]

    2. Place 'Product'[Item type] in the rows of a table visual

    3. Create this measure and place it in the visual

    Measure =
COUNTROWS (
    FILTER ( DISTINCT ( 'Product'[Part #] ), CALCULATE ( COUNTROWS ( Sales ) ) > 1 )
)

    Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

    Contact me privately for support with any larger-scale BI needs, tutoring, etc.

    Cheers