Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
saie5073
Frequent Visitor

Totals by Month and Previous Quarter

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:

 

 UserTotal
Jan-23A2
Feb-23A0
Mar-23A3
Apr-23A4
Jan-23B6
Feb-23B0
Mar-23B0
Apr-23B0
Jan-23C0
Feb-23C0
Mar-23C1
Apr-23C0

 

Any idea how I can produce these results? 

 

 UserTotalPoints
Jan-23A21
Feb-23A01
Mar-23A32
Apr-23A42
Jan-23B63
Feb-23B01
Mar-23B01
Apr-23B01
Jan-23C01
Feb-23C01
Mar-23C11
Apr-23C01

 

User1Q points2Q points (to date)
A22
B31
C11
4 REPLIES 4
saie5073
Frequent Visitor

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?

v-yangliu-msft
Community Support
Community Support

Hi  @saie5073 ,

 

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:

vyangliumsft_0-1675405057219.png

 

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

Ashish_Mathur
Super User
Super User

Hi,

You may download my PBI file from here.

hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Greg_Deckler
Super User
Super User

@saie5073 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

Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Fabric Monthly Update - May 2024

Check out the May 2024 Fabric update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.