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.
Goodmorning,
The situation I have is that I'm ranking Sales Persons by Total Sales and then taking their previous month's rank and comparing it to the current month. The way I've done this is in the formulas below.
However, I'm looking for help transitioning my current formula to a more dynamic format. I would prefer to have this all on one table/matrix, but my current set up forces me to create different tables because I'm using the SELECTEDVALUE function. I tried making a generic "Previous Month Rank" measure but couldn't due to a table error.
Any suggestions would be great!
Thank you!
Sales Rank =
IF( HASONEVALUE( 'Sales Index'[Sales Person] ),
RANKX(
ALL( 'Sales Index'[Sales Person] ) ,
'Sales Data'[Sales], ,
DESC
),
BLANK())
Rank Change =
Var CurrentRank = [Sales Rank]
Var PreviousRank =
IF(SELECTEDVALUE('Sales Data'[Month]) = "February",
CALCULATE(
[Sales Rank],
'Sales Data'[Month] IN { "January" }, ALL('Sales Data'[Month No.])),
IF(SELECTEDVALUE('Sales Data'[Month]) = "March",
CALCULATE(
[Sales Rank],
'Sales Data'[Month] IN { "February" }, ALL('Sales Data'[Month No.])),
IF(SELECTEDVALUE('Sales Data'[Month]) = "April",
CALCULATE(
[Sales Rank],
'Sales Data'[Month] IN { "March" }, ALL('Sales Data'[Month No.])),
IF(SELECTEDVALUE('Sales Data'[Month]) = "May",
CALCULATE(
[Sales Rank],
'Sales Data'[Month] IN { "April" }, ALL('Sales Data'[Month No.])),
IF(SELECTEDVALUE('Sales Data'[Month]) = "June",
CALCULATE(
[Sales Rank],
'Sales Data'[Month] IN { "May" }, ALL('Sales Data'[Month No.])),
IF(SELECTEDVALUE('Sales Data'[Month]) = "July",
CALCULATE(
[Sales Rank],
'Sales Data'[Month] IN { "June" }, ALL('Sales Data'[Month No.])),
IF(SELECTEDVALUE('Sales Data'[Month]) = "August",
CALCULATE(
[Sales Rank],
'Sales Data'[Month] IN { "July" }, ALL('Sales Data'[Month No.])),
IF(SELECTEDVALUE('Sales Data'[Month]) = "September",
CALCULATE(
[Sales Rank],
'Sales Data'[Month] IN { "August" }, ALL('Sales Data'[Month No.])),
IF(SELECTEDVALUE('Sales Data'[Month]) = "October",
CALCULATE(
[Sales Rank],
'Sales Data'[Month] IN { "September" }, ALL('Sales Data'[Month No.])),
IF(SELECTEDVALUE('Sales Data'[Month]) = "November",
CALCULATE(
[Sales Rank],
'Sales Data'[Month] IN { "October" }, ALL('Sales Data'[Month No.])),
IF(SELECTEDVALUE('Sales Data'[Month]) = "December",
CALCULATE(
[Sales Rank],
'Sales Data'[Month] IN { "November" }, ALL('Sales Data'[Month No.])),
IF(SELECTEDVALUE('Sales Data'[Month]) = "January",
CALCULATE(
[Sales Rank],
'Sales Data'[Month] IN { "December" }, ALL('Sales Data'[Month No.]))," "
) ) ) ) ) ) ) ) ) ) ) )
Return
CALCULATE(PreviousRank - CurrentRank)
Hi @Anonymous,
Kindly share your sample data to me.
Regards,
Frank
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 |
---|---|
106 | |
94 | |
75 | |
62 | |
50 |
User | Count |
---|---|
147 | |
106 | |
104 | |
87 | |
61 |