accumulate (distinctivly) the amount of clients who have stock bigger than 0

[pg1980]

Hi,

I need to create a meausure to incorporate in the TABLE visualization (which varies according to the filter from the table DATE) so that I can accumulate (count distinctivly) the amount of clients who have stock > 0. If the sum in the stock column is more than 1, it should count it as just one: example: if for client D the sum of stock column is 2 it should count as only one client.

I usea a Table Dates (column: year-month) to filter the main visualization:

TableDates = ADDCOLUMNS ( CALENDAR (FIRSTDATE(Sheet1[DATE]), TODAY()), "year", YEAR ( [Date] ), "MonthNumber", FORMAT ( [Date], "MM" ), "year-month", FORMAT ( [Date], "YYYY-MM" ), "month-year", FORMAT ( [Date], "MM-'YY" ) )

This is the main table:

Client	DATE	Category	product	stock
A	30-ene-20	Category1	1001	1
A	8-may-20	Category1	1001	-1
A	10-sep-20	Category1	1002	1
A	1-nov-20	Category1	1002	-1
A	8-nov-20	Category1	1003	1
A	8-may-21	Category1	1003	-1
A	11-jun-21	Category1	1004	1
A	8-nov-21	Category1	1004	-1
A	11-dic-21	Category1	1005	1
B	30-ene-20	Category2	3001	1
B	15-mar-20	Category2	3001	-1
B	30-jul-20	Category2	3002	1
B	30-jul-20	Category2	3002	-1
C	7-feb-20	Category3	2001	1
C	7-ago-20	Category3	2001	-1
C	7-feb-21	Category3	2002	1
C	16-abr-21	Category3	2002	-1
C	7-ago-21	Category3	2003	1
C	7-feb-22	Category3	2003	-1
C	15-abr-22	Category3	2004	1
D	30-jul-20	Category1	4001	1
D	30-ene-21	Category1	4001	-1
D	30-jul-21	Category1	4002	1
D	3-nov-21	Category1	4002	-1
D	3-nov-21	Category1	4003	1
D	2-may-22	Category1	4004	1
D	3-may-22	Category1	4005	1

I need this results:

Category	Clients
Category1	2
Category2	0
Category3	1
total	3

[tamerj1]

Hi @pg1980 
Please use

Clients = 
SUMX ( 
    VALUES ( Sheet1[Category] ),
    CALCULATE ( 
        IF (
            SUM ( Sheet1[stock] ) > 0,
            DISTINCTCOUNT ( Sheet1[Client] ),
            0
        )
    )
)

[pg1980]

Thank you @tamerj1 ! but if i have to accumulate the active clients, and I use a filter ("year-month") in "2020-07" I have this result:

This is the measure i used:

Amount of client =

CALCULATE(

    [Clients],

     FILTER(

ALL(TableDates[Date]),

(TableDates[Date])<=MAX(TableDates[Date])))

[tamerj1]

@pg1980 
So what do you mean to say?

[pg1980]

Hi! What I need to do is to accumulate the quantity of active clients month by month. I have used your measure "Clients" by the measure described before ("Amount of client") and for the "2020-07" it´s adding Client A, but it shouldn´t. I am also using a Table Dates. Do you think you could help me? Has it ever happened to you?

[tamerj1]

Noted @pg1980 

please try

Clients =
VAR CurrentDate =
    MAX ( DateTable[Date] )
RETURN
    CALCULATE (
        SUMX (
            VALUES ( Sheet1[Category] ),
            CALCULATE (
                IF ( SUM ( Sheet1[stock] ) > 0, DISTINCTCOUNT ( Sheet1[Client] ), 0 )
            )
        ),
        DateTable[Date] <= CurrentDate
    )

[pg1980]

No, sorry. It has the same problem. It is counting one more client (Client A is cero and client D is 1, so category 1 must be 1) 

[tamerj1]

@pg1980 

If not working then we need the cross join with date

Clients =
VAR CurrentDate =
    MAX ( DateTable[Date] )
RETURN
    CALCULATE (
        SUMX (
            SUMMARIZE ( Sheet1, Sheet1[Category], Sheet1[Client] ),
            CALCULATE (
                IF ( SUM ( Sheet1[stock] ) > 0, DISTINCTCOUNT ( Sheet1[Client] ), 0 )
            )
        ),
        DateTable[Date] <= CurrentDate
    )

[pg1980]

Its duplicated Sheet1[Category] in SUMMARIZE , should I change to an other parameter?

[tamerj1 · Accepted solution]

Yes change to Client

[tamerj1]

@pg1980 

If not working please try

Clients =
VAR CurrentDate =
    MAX ( DateTable[Date] )
RETURN
    CALCULATE (
        SUMX (
            CROSSJOIN (
                VALUES ( DateTable[Date] ),
                SUMMARIZE ( Sheet1, Sheet1[Category], Sheet1[Client] )
            ),
            CALCULATE (
                IF ( SUM ( Sheet1[stock] ) > 0, DISTINCTCOUNT ( Sheet1[Client] ), 0 )
            )
        ),
        DateTable[Date] <= CurrentDate
    )

[pg1980]

It is working with this measure:

Clients =

VAR CurrentDate =

MAX ( TableDates[Date] )

RETURN

CALCULATE (

SUMX (

SUMMARIZE ( Sheet1, Sheet1[Category], Sheet1[Client]),

CALCULATE (

IF ( SUM ( Sheet1[stock] ) > 0, DISTINCTCOUNT ( Sheet1[Client] ), 0 )

)

),

TableDates[Date] <= CurrentDate

)

Like you said before. Thank you very much!

[tamerj1]

Hi @pg1980

would you please clarify how did you reach to Category 1 > 2?

[pg1980]

In this case if you sum the "stock" column, the answer for Category 1 is 4 but i need to count distinctivly the clients. So in Category 1, you have 2 clients: A an D.

for example: if for client D the sum of stock column is 2 it should count as only one client