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 have a table called Transactions which has a list of all our transactions and associated columns for each transaction:
e.g.
Transaction ID || Customer ID || Product ID || Year-Month
Transaction1 || Customer1 || Product1 || 2019-01
Transaction2 || Customer1 || Product2 || 2019-01
Transaction3 || Customer2 || Product2 || 2019-01
Transaction4 || Customer2 || Product2 || 2019-01
Transaction5 || Customer2 || Product3 || 2019-01
Transaction6 || Customer3 || Product1 || 2019-01
Transaction7 || Customer1 || Product1 || 2019-02
Transaction8 || Customer2 || Product2 || 2019-02
Transaction9 || Customer1 || Product2 || 2019-02
Transaction10 || Customer3 || Product1 || 2019-02
I would somehow like to create a measure/column which will count the number of transactions that a customer has done in a month, so that I can create the following visual (matrix) in the report:
"Distinct count of Customer IDs by Number of transactions in month, Product ID and Year-Month":
--------------------------- || 2019-01 || 2019-02 .............
1 transaction in month || 1 || 2
---- Product1 || 1 || 1
---- Product2 || 0 || 2
---- Product3 || 0 || 0
2 transactions in month || 1 || 1
---- Product1 || 1 || 1
---- Product2 || 1 || 1
---- Product3 || 0 || 0
3 transactions in month || 1 || 0
---- Product1 || 0 || 0
---- Product2 || 1 || 0
---- Product3 || 1 || 0
I have tried various different measures/columns to try and get this to work but I can't seem to get it right. Please can anyone help on this?
Thank you in advance.
Solved! Go to Solution.
Hi @Anonymous
First you can create a column
Number of transactions in month2 = CALCULATE ( DISTINCTCOUNT ( 'Table'[Transaction ID] ), ALLEXCEPT ( 'Table', 'Table'[Year-Month], 'Table'[Customer ID] ) )
Then add columns in a matrix as below
Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Anonymous
Is this problem sloved?
If it is sloved, could you kindly accept it as a solution to close this case?
If not, please let me know.
Best Regards
Maggie
Hi @Anonymous
First you can create a column
Number of transactions in month2 = CALCULATE ( DISTINCTCOUNT ( 'Table'[Transaction ID] ), ALLEXCEPT ( 'Table', 'Table'[Year-Month], 'Table'[Customer ID] ) )
Then add columns in a matrix as below
Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
HI @Anonymous ,
I don't think you need a measure.
Did you try usingMatrix visual and Did you drag the field like this?
Based on your sample data I get the output somehting like this>
Let me know if this works for you.
Thanks,
Tejaswi
Hi @Anonymous ,
Thank you for your reply, however I do not want to group Transaction ID in the rows, I want to group Number of Transactions in Month.
So the 2nd table in my original post is what I would like to get to - showing number of distinct Customer IDs who have done X transactions in month, then split by product.
E.g. in my example above, in Jan 2019, one customer did one transaction in the month (Customer3), one did 2 transactions in the month (Customer1) and one did 3 transactions in the month (Customer2). Similarly in Feb, two customers did 1 transaction in the month and one customer did 2 transactions in the month.
I hope that makes sense!
Hi @Anonymous ,
Try this calculated Column to give the count of Cust ID
Occurences of Cust ID = CALCULATE ( COUNT ( 'Table (5)'[Year-Month] ), FILTER ( ALLEXCEPT ( 'Table (5)', 'Table (5)'[Customer ID] ), MONTH ( 'Table (5)'[Year-Month] ) = MONTH ( EARLIER ( 'Table (5)'[Year-Month] ) ) ) )
Also since you have a Unique Transaction ID, getting the matrix like the one you showed will be little diffcult.
But you can give a shot with this formula.
If you want you can show something like this.
Thanks,
Tejaswi
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 |
---|---|
110 | |
97 | |
78 | |
64 | |
55 |
User | Count |
---|---|
143 | |
109 | |
89 | |
84 | |
66 |