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

Be 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

Reply
Anonymous
Not applicable

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
Anonymous
Not applicable

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

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

@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


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!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.

ArunFabCon

Microsoft Fabric Community Conference 2025

Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.

December 2024

A Year in Review - December 2024

Find out what content was popular in the Fabric community during 2024.