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 would like to know how to create this auxiliar table where I have a count of each unique customers displayed by how many times they completed a transaction.
Many thanks in advance,
Rui
Solved! Go to Solution.
Hi, @RMSLUIS
Column:
Segment = SWITCH(True(),
[Transaction]>=4,"High",
[Transaction]<4&&[Transaction]>=2,"Medium",
[Transaction]<=1,"Low")
Best Regards,
Community Support Team _Charlotte
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@v-zhangti I'd like to borrow your file, thanks!
1st, create a table via the below code :
Segment =
SELECTCOLUMNS(
{
( "High", 1 ) ,
( "Medium" , 2 ) ,
( "Low" ,3 )
} ,
"Segment" , [Value1] , "Orderby" , [Value2]
)
2nd, create a measure
# Customers =
VAR _customerTbl =
ADDCOLUMNS (
VALUES ( 'Transaction'[Customer ID] ),
"@Transaction",
CALCULATE (
DISTINCTCOUNT ( 'Transaction'[Transaction ID] )
)
)
VAR _Segment =
MAX ( 'Segment'[Segment] )
RETURN
IF(
HASONEVALUE( 'Segment'[Segment] ) ,
SWITCH (
TRUE (),
_Segment = "High",
COUNTROWS (
FILTER (
_customerTbl,
[@Transaction] >= 4
)
),
_Segment = "Medium",
COUNTROWS (
FILTER (
_customerTbl,
[@Transaction] >= 2
&& [@Transaction] < 4
)
),
COUNTROWS (
FILTER (
_customerTbl,
[@Transaction] < 2
)
)
),
COUNTROWS( _customerTbl )
)
3rd, put the column Segment[Segment] and measure into Matrix.
Hi, @RMSLUIS
You can try the following methods.
Table:
Table 2 =
SUMMARIZE (
'Table',
'Table'[Customer ID],
"Transaction",
CALCULATE (
COUNT ( 'Table'[Customer ID] ),
ALLEXCEPT ( 'Table', 'Table'[Customer ID] )
)
)
Column:
Customer = CALCULATE(COUNT('Table 2'[Transaction]),ALLEXCEPT('Table 2','Table 2'[Transaction]))
Result:
Is this the result you expect?
Best Regards,
Community Support Team _Charlotte
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi,
It works perfectly (by table).
If I want in the same table to add a new collum where I segment the customers by frequency. I mean like this....
Thank you very much!
Rui
Hi, @RMSLUIS
Column:
Segment = SWITCH(True(),
[Transaction]>=4,"High",
[Transaction]<4&&[Transaction]>=2,"Medium",
[Transaction]<=1,"Low")
Best Regards,
Community Support Team _Charlotte
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
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 |
---|---|
46 | |
26 | |
21 | |
12 | |
8 |
User | Count |
---|---|
75 | |
52 | |
45 | |
15 | |
12 |