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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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