Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
Here is my example table:
User ID# | TransactionID |
1 | a |
1 | b |
2 | c |
3 | d |
4 | e |
4 | f |
4 | g |
I want to have a measure to use in a card that shows the # and or % of customers with more than 2 transactions.
The result of the example table would be:
2 out of the 4 users had 2 or more transactions.
50% of the users had 2 or more transactions
Solved! Go to Solution.
Try these two measures,
1. Percentage
Percentage =
VAR _countOfUsers =
DISTINCTCOUNT ( 'Table'[User ID#] )
VAR _tempTable =
SUMMARIZECOLUMNS ( 'Table'[User ID#], "@Count", COUNT ( 'Table'[User ID#] ) )
VAR _countOfTwoTransactionUsers =
CALCULATE ( COUNTROWS ( FILTER ( _tempTable, [@Count] >= 2 ) ) )
VAR _output =
DIVIDE ( _countOfTwoTransactionUsers, _countOfUsers )
RETURN
_output
2. No.of Users with two or more records
No.Of Users =
VAR _tempTable =
SUMMARIZECOLUMNS ( 'Table'[User ID#], "@Count", COUNT ( 'Table'[User ID#] ) )
VAR _countOfTwoTransactionUsers =
CALCULATE ( COUNTROWS ( FILTER ( _tempTable, [@Count] >= 2 ) ) )
RETURN
_countOfTwoTransactionUsers
I'd like to ask a follow up question please. Using the same table as an example:
User ID# | TransactionID | Gender | Age |
1 | a | M | 29 |
1 | b | M | 29 |
2 | c | M | 41 |
3 | d | F | 25 |
4 | e | F | 19 |
4 | f | F | 19 |
4 | g | F | 19 |
I've created a page with demographics of the unique users and now I can include the # and % of those with 2 or more transactions. Now, I've been asked to also provide the same demograhics for the users that have 2 or more transactions. I can create a new post for this if you prefer.
Hi,
Show the exact result that you are expecting.
I'd like to add an additional filter so that the user has the option to see the demogrpahics of the "repeat" clients. My dataset is not live so I can only share a screenshot.
Please share more details. Separate thread also fine.
that worked perfectly!!!
Thank-you!
Try these two measures,
1. Percentage
Percentage =
VAR _countOfUsers =
DISTINCTCOUNT ( 'Table'[User ID#] )
VAR _tempTable =
SUMMARIZECOLUMNS ( 'Table'[User ID#], "@Count", COUNT ( 'Table'[User ID#] ) )
VAR _countOfTwoTransactionUsers =
CALCULATE ( COUNTROWS ( FILTER ( _tempTable, [@Count] >= 2 ) ) )
VAR _output =
DIVIDE ( _countOfTwoTransactionUsers, _countOfUsers )
RETURN
_output
2. No.of Users with two or more records
No.Of Users =
VAR _tempTable =
SUMMARIZECOLUMNS ( 'Table'[User ID#], "@Count", COUNT ( 'Table'[User ID#] ) )
VAR _countOfTwoTransactionUsers =
CALCULATE ( COUNTROWS ( FILTER ( _tempTable, [@Count] >= 2 ) ) )
RETURN
_countOfTwoTransactionUsers
User | Count |
---|---|
85 | |
74 | |
71 | |
68 | |
56 |
User | Count |
---|---|
98 | |
96 | |
92 | |
78 | |
70 |