Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
pg1980
Helper II
Helper II

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

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:

ClientDATECategoryproductstock
A30-ene-20Category1 10011
A8-may-20Category1 1001-1
A10-sep-20Category1 10021
A1-nov-20Category1 1002-1
A8-nov-20Category1 10031
A8-may-21Category1 1003-1
A11-jun-21Category1 10041
A8-nov-21Category1 1004-1
A11-dic-21Category1 10051
B30-ene-20Category2  30011
B15-mar-20Category2  3001-1
B30-jul-20Category2  30021
B30-jul-20Category2  3002-1
C7-feb-20Category3   20011
C7-ago-20Category3   2001-1
C7-feb-21Category3   20021
C16-abr-21Category3   2002-1
C7-ago-21Category3   20031
C7-feb-22Category3   2003-1
C15-abr-22Category3   20041
D30-jul-20Category1 40011
D30-ene-21Category1 4001-1
D30-jul-21Category1 40021
D3-nov-21Category1 4002-1
D3-nov-21Category1 40031
D2-may-22Category1 40041
D3-may-22Category1 40051

 

I need this results:

 

CategoryClients
Category1 2
Category2  0
Category3   1
total3
1 ACCEPTED SOLUTION

Yes change to Client

View solution in original post

13 REPLIES 13
tamerj1
Super User
Super User

Hi @pg1980 
Please use

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

1.png

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:


amount client.PNG

This is the measure i used:
 
Amount of client =
CALCULATE(
    [Clients],
     FILTER(
ALL(TableDates[Date]),
(TableDates[Date])<=MAX(TableDates[Date])))
 

@pg1980 
So what do you mean to say?

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?

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
    )

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) 

 

client.PNG

@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
    )

 

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

Yes change to Client

@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
    )

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
Super User
Super User

Hi @pg1980

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

 

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors