Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
First of all, I just want to say, I know this is a frequently asked question on the forum. I assure you I have checked over 30 forum subjects related to this problem but none of them actually solved my problem.
So thank you to whoever can help out! 🙂
I have a table called Commissions summary that are for sales reps This table generates a summary row for each sales rep for each month. I want to rank the sales rep only by each month, hence, my main filter here is by date.
-------------------------------------------------------------------
The table can be simplified down to this
Sales Reps Date Target Attainment
John Deer 10/01/2019 53%
John Deer 09/01/2019 69%
Jane Doe 10/01/2019 165%
Jane Doe 09/01/2019 82%
Adam Smith 10/01/2019 74%
Adam Amith 09/01/2019 73%
---------------------------------------------------------------------
My goal is to show my table like this (let's say for October)
Sales Reps Date Target Attainment Rank
Jane Doe 10/01/2019 165% 1
Adam Smith 10/01/2019 74% 2
John Deer 10/01/2019 53% 3
John Deer 09/01/2019 69%
Jane Doe 09/01/2019 82%
Adam Amith 09/01/2019 73%
---------------------------------------------------------------------
This is the Rankx formula I am using
Sales Ranks = RANKX(
FILTER(
ALLSELECTED('Commission Summary'),'Commission Summary'[Date]=10/01/2019),
CALCULATE(SUM('Commission Summary'[Target Attainment])),,DESC)
As I mentioned earlier, I am only filtering my date to October.
Please help 😞
Solved! Go to Solution.
@Anonymous try this measure
Rank =
RANKX (
ALLSELECTED ( 'Table (2)'[Sales Reps] ),
CALCULATE( SUM ( 'Table (2)'[Target Attainment] ) ), ,
DESC,
Skip
)
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
Hi,
Parry2K's formula should work. Just ensure that you create a Calendar Table and build a relationship from the Date column of your data table to the data column of the Calendar Table. In the Calendar Table. create columns for Year and Month. Build slicers from the Calendar Table for Year and Month. Select any Year and select October.
Hope this helps.
Please see if this works:
1. Add calculated column Month = FORMAT('Commission Summary''[date], "MM")
2. Measure: Sales Ranks = RANKX(ALL('Commission Summary'), CALCULATE(sum('Commission Summary'[Target Attainment]), VALUES('Commission Summary'[Month])),,DESC)
I assumed that you need to rank achievements by month.
@Anonymous try this measure
Rank =
RANKX (
ALLSELECTED ( 'Table (2)'[Sales Reps] ),
CALCULATE( SUM ( 'Table (2)'[Target Attainment] ) ), ,
DESC,
Skip
)
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
104 | |
101 | |
79 | |
72 | |
64 |
User | Count |
---|---|
142 | |
108 | |
101 | |
81 | |
74 |