Count of bill where bought two or more group of product

[Claster]

Hello. I need to calculate count of bill where bought two or more group of product

For example, I filtered two groups, I need to count the number of bill where there are these 2 groups.

result: CountOfBill = 2

I tried like this but it's don't work =(

CountOfBill =
VAR F1 = IF(ISFILTERED('Sales'[ProductGroup]);FIRSTNONBLANK('Sales'[ProductGroup];TRUE()))
VAR F2 = IF(ISFILTERED('Sales'[ProductGroup]);LASTNONBLANK('Sales'[ProductGroup];TRUE()))
RETURN CALCULATE(DISTINCTCOUNT([BillNumber]);FILTER('Sales';[ProductGroup] = F1 && [ProductGroup] = F2))

[v-ljerr-msft]

Hi @Claster,

Based on my test, the formula below should work in your scenario. 

CountOfBill =
COUNTROWS (
    FILTER (
        SUMMARIZE (
            Sales,
            Sales[BillNumber],
            "CountOfGroup", DISTINCTCOUNT ( Sales[ProductGroup] )
        ),
        [CountOfGroup] >= 2
    )
)

Regards

[Claster]

Thanks for help, but almost right

If I chose 3 ProductGroup the CountOfBill should be 0 (zero)  because I don't have bill where customer bought 3 group in one bill

I think like this

CountOfBill =
VAR ChouseCountGroup = COUNTROWS(VALUES('Sales'[ProductGroup]))
VAR F =

COUNTROWS (

    FILTER(
        SUMMARIZE(
                   'Sales';
                   Sales[BillNumber];
                   "CountOfGroup";DISTINCTCOUNT ( Sales[ProductGroup] ));
         [CountOfGroup] = ChouseCountGroup)
)
RETURN IF(F>0;F;0)

[v-ljerr-msft · Accepted solution]

Hi @Claster,

The formula below should work in this scenario. 

CountOfBill = 
VAR countOfSelectedGroup =
    DISTINCTCOUNT ( Sales[ProductGroup] )
RETURN
    COUNTROWS (
        FILTER (
            SUMMARIZE (
                Sales,
                Sales[BillNumber],
                "CountOfGroup", DISTINCTCOUNT ( Sales[ProductGroup] )
            ),
            [CountOfGroup] >= countOfSelectedGroup
        )
    ) + 0

Regards

[Claster]

Thank you so much !

[v-ljerr-msft]

Great to help!