Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 |
Solved! Go to Solution.
Hi @pg1980
Please use
Clients =
SUMX (
VALUES ( Sheet1[Category] ),
CALCULATE (
IF (
SUM ( Sheet1[stock] ) > 0,
DISTINCTCOUNT ( Sheet1[Client] ),
0
)
)
)
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:
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)
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
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:
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
37 | |
25 | |
19 | |
14 | |
8 |
User | Count |
---|---|
69 | |
48 | |
44 | |
19 | |
15 |