Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
I am building a small, fairly simple report for my finance department. They do analysis of total AR and AP transactions, per employee per day, per month and year to date.
The data set is fairly simple and straight forward. Basically 7 columns. One column is a user id column, and I created a new column converting user names to Full Name. To get total count of transactions per user, I wrote a very simple measure that is just a COUNT of all rows on the voucher column.
Visual is a matrix view, with stepped layout and +/- turned off.
I used the above measure on the matrix visual to get total transactions per user. On a per user level, I get row total and grand total.
What I need to do is get a PERCENT of transaction count per user. So if User A had 176 transactions for May, and the total count for May is 3709, I need to divide the user count by the total for that month across all users and have a percent.
What I am wondering is, the possibility of a measure or calculated column that would supply the percentage per user… but that seems to require me to through code, get that individual count and total count… I do not know if that is possible or how to do that.
See report here: https://tinyurl.com/mrspct9t
Solved! Go to Solution.
Hi @mwbowers ,
Please try:
Percentage =
var _total = CALCULATE(COUNT('AR Transactions'[Voucher]),FILTER(ALL('AR Transactions'),MONTH('AR Transactions'[CreatedDate])=MONTH(MAX('AR Transactions'[CreatedDate]))))
return DIVIDE([ARTransCount],_total)
Then change the format of the measure to percentage:
Final output:
Best Regards,
Jianbo Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @mwbowers ,
Please try:
Percentage =
var _total = CALCULATE(COUNT('AR Transactions'[Voucher]),FILTER(ALL('AR Transactions'),MONTH('AR Transactions'[CreatedDate])=MONTH(MAX('AR Transactions'[CreatedDate]))))
return DIVIDE([ARTransCount],_total)
Then change the format of the measure to percentage:
Final output:
Best Regards,
Jianbo Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
User | Count |
---|---|
93 | |
85 | |
76 | |
66 | |
62 |
User | Count |
---|---|
112 | |
97 | |
95 | |
64 | |
58 |