Distinct Count (customer) x Brand x Threshold

[tarcisiomu]

Hello guys,

I am struggling to find a way to make the following calculation.

I need to count the number of active customer per month per each brand, but each brand has a specific threshold. For instance:

Customer 1789 ordered 5 units of Brand X

Customer 9867 ordered 3 units of Brand X

Customer 2341 ordered 30 units of Brand X

Customer 2341 ordered 12 units of Brand Y

Considering the threshold for product X is >=5 and for Y is >=10 I would have two customers for Brand X (1789 and 2341) and one customer for Brand Y (2341).

I am using this function, but when I put the second condition (brand) it does not work...

=CALCULATE
(
DISTINCTCOUNT([Cliente]);
DATESBETWEEN(Tabela1[Data]; "01/01/2015"; "31/12/2015");
Tabela1[Quantidade]>=5
)

Any help from the genius out there? 🙂

[Phil_Seamark]

Can you please post a small sample set of your data (including column names) and we can try and create a calculated measure for you.

[tarcisiomu]

Hello, Phil

This is a piece of the data:

Order #	Date	Customer	Brand	Quantity
469669	08/02/2017	1009	CRD	3
469678	10/02/2017	1009	CRD	2
469669	12/02/2017	1009	CRD	-3
470601	12/02/2017	1015	JIM	1
470601	12/02/2017	1015	JIM	2
470601	12/02/2017	1015	JIM	2
470612	12/02/2017	1015	CEL	9
470613	12/02/2017	1015	CEL	2
470612	12/02/2017	1015	CEL	-1
470833	14/02/2017	1061	CRD	5
470833	14/02/2017	1061	FEN	9
470833	14/02/2017	1061	FEN	1
470887	14/02/2017	1262	JIM	3
470887	14/02/2017	1262	JIM	2

CRD THRESHOLD	>=5
JIM THRESHOLD	>=5
CEL THRESHOLD	>=10
FEN THRESHOLD	>=10

Considering the threshold for each brand, the final result should be:

2 customers for brand JIM (1015 and 1262)

1 customer for brand CEL (1015)

1 customer for brand CRD (1061) we need to discard the customer 1009 considering the return of 3 units, therefore the final result is 2 no reaching the threshold

1 customer for brand FEN (1061)

I hope this helps you to help me 🙂

[Phil_Seamark]

How does this look?

There is an element of hardcoding the rules as you can see.  These would be better kept in a table so you can update.  Unless you know for sure they never change.

Add a new table as follows: I assumed the table you provided was called 'Brand'

Summary Table = FILTER(
            SUMMARIZE(
                    'Brand',
                    Brand[Brand],
                    Brand[Customer],
                    "Orders",sum(Brand[Quantity])
                    ),
                    ([Brand] = "JIM" && [Orders]>=5) ||
                    ([Brand] = "CRD" && [Orders]>=5) ||
                    ([Brand] = "CEL" && [Orders]>=10) ||
                    ([Brand] = "FEN" && [Orders]>=10) 
                    )