March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early bird discount ends December 31.
Register NowBe one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now
I am tracking a list of users' total sales for incentive purposes. Incentives are paid on a monthly & quarterly basis, using a point system to determine the incentive. Points are assigned as follows:
0-2 sales : 1 point
3-5 sales : 2 points
5+ sales : 3 points
For some reason I am struggling to figure out how to calculate the total number of points by month & by quarter for each user, including the quarter-to-date totals. Here is an example:
User | Total | |
Jan-23 | A | 2 |
Feb-23 | A | 0 |
Mar-23 | A | 3 |
Apr-23 | A | 4 |
Jan-23 | B | 6 |
Feb-23 | B | 0 |
Mar-23 | B | 0 |
Apr-23 | B | 0 |
Jan-23 | C | 0 |
Feb-23 | C | 0 |
Mar-23 | C | 1 |
Apr-23 | C | 0 |
Any idea how I can produce these results?
User | Total | Points | |
Jan-23 | A | 2 | 1 |
Feb-23 | A | 0 | 1 |
Mar-23 | A | 3 | 2 |
Apr-23 | A | 4 | 2 |
Jan-23 | B | 6 | 3 |
Feb-23 | B | 0 | 1 |
Mar-23 | B | 0 | 1 |
Apr-23 | B | 0 | 1 |
Jan-23 | C | 0 | 1 |
Feb-23 | C | 0 | 1 |
Mar-23 | C | 1 | 1 |
Apr-23 | C | 0 | 1 |
User | 1Q points | 2Q points (to date) |
A | 2 | 2 |
B | 3 | 1 |
C | 1 | 1 |
In my case, the quarterly amount is not a sum of the points, it is based on the sum of the total sales. For example, User A had 5 total sales in Q1, so it should be 2 points. This is where I am struggling the most. I can get the monthly totals working, but I can't figure out how to do it quarterly (and show it in the same matrix if possible). Any ideas?
Hi @Anonymous ,
Here are the steps you can follow:
1. Create calculated column.
Month-Year =
FORMAT(
'Table'[Date],"mmm")
&"-"&
FORMAT('Table'[Date],"yy")
2. Create measure.
Value =
SUMX(
FILTER(ALL('Table'),
'Table'[Month-Year]=MAX('Table'[Month-Year])&&'Table'[User]=MAX('Table'[User])),[Total])
Point =
SWITCH(
TRUE(),
[Value]>=0&&[Value]<=2,1,
[Value]>=3&&[Value]<=5,2,3)
1Q points =
SUMX(
FILTER(ALL('Table'),
'Table'[User]=MAX('Table'[User])
&&
YEAR('Table'[Date])=YEAR(MAX('Table'[Date]))&&
QUARTER('Table'[Date])=1
),[Point])
2Q points =
SUMX(
FILTER(ALL('Table'),
'Table'[User]=MAX('Table'[User])
&&
YEAR('Table'[Date])=YEAR(MAX('Table'[Date]))&&
QUARTER('Table'[Date])=2
),[Point])
3. Result:
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
Hi,
You may download my PBI file from here.
hope this helps.
@Anonymous The first one should be something like:
Points Measure =
VAR __Sum = SUM('Table'[Total])
VAR __Result =
SWITCH(TRUE(),
__Sum < 3, 1,
__Sum < 6, 2,
3
)
RETURN
__Result
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
User | Count |
---|---|
93 | |
90 | |
86 | |
77 | |
49 |
User | Count |
---|---|
164 | |
149 | |
101 | |
73 | |
56 |