'How to count (distinctly) the amount of clients who have stock > 0

I need help with Power BI.

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 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.

Also I need to acumulated (the amount of active clients) when i filter by a Table Dates (column: year-month).

            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

Thanks!

Solution 1:^[1]

Used 3 measures -

1. count_cust = DISTINCTCOUNT(Sheet1[Client])
2. sum_stock = if(sum(Sheet1[stock]) > 0, 1, 0)
3. cust_cntxsum_stock = [count_cust]*Sheet1[sum_stock]

Multiplied, measure 1 and 2, to get desired counts.

Visual with field: category and measure: cust_cntxsum_stock as needed output -

Visual to show all measures -

Adding below for correcting Totals -

Measure1 = sum(Sheet1[stock])
Measure2 = calculate(if(Sheet1[Measure1]>0, DISTINCTCOUNT(Sheet1[Client]),0) )
Measure2_adj = IF(COUNTROWS(VALUES(Sheet1[Category]))=1,[Measure2],SUMX(VALUES(Sheet1[Category]),[Measure2])

Desired output -

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution	Source
Solution 1

'How to count (distinctly) the amount of clients who have stock > 0

Solution 1:[1]

Sources

Related Questions

Solution 1:^[1]