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.
DATA | Result needed | ||||||
opportunity | categtegory | ARR | week start | TOPN- commit | TOPN- upside | ||
a | commit | 1000 | 5/7/2021 | 2 | |||
b | commit | 24434 | 5/7/2021 | 1 | |||
h | upside | 1000 | 5/7/2021 | 2 | |||
c | upside | 3424 | 5/7/2021 | 1 | |||
d | commit | 0 | 6/7/2021 | 2 | |||
e | upside | 244324 | 6/7/2021 | 1 | |||
f | commit | 231 | 6/7/2021 | 1 | |||
g | upside | 4355 | 7/7/2021 | 1 | |||
h | commit | 214324 | 7/7/2021 | 1 | |||
j | commit | 342 | 7/7/2021 | 2 | |||
k | upside | 3525 | 7/7/2021 | 2 |
Need help in fining top 10 opportunities for "commit" and top5 for "upside" as per ARR for every week start.
=> created the below mentioned logic but it is not considerig week start and will give TON for whole data.
Also it is ignoring ARR=0 while raking the opportunities, not able to undertsand the reason.
Solved! Go to Solution.
Hi @Anonymous ,
Please try to use the following measure:
Upside_top_weekly_5 =
VAR RANKING2 =
IF (
Opportunity[Category] = "Upside"
&& Opportunity[ARR] <> BLANK (),
RANKX (
ALL ( Opportunity[Opportunity] ),
CALCULATE (
SUM ( Opportunity[ARR] ),
FILTER (
Opportunity,
Opportunity[Category] = "Upside"
&& Opportunity[ARR] <> BLANK ()
)
),
,
DESC,
DENSE
),
BLANK ()
)
RETURN
IF ( RANKING2 < 6, RANKING2, BLANK () )
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Best Regards,
Dedmon Dai
I think this will be simpler if you break it into a couple of measures.
I created an [Upside ARR] to only return the value of ARR for category = upside
Hi @Anonymous ,
Please try to use the following measure:
Upside_top_weekly_5 =
VAR RANKING2 =
IF (
Opportunity[Category] = "Upside"
&& Opportunity[ARR] <> BLANK (),
RANKX (
ALL ( Opportunity[Opportunity] ),
CALCULATE (
SUM ( Opportunity[ARR] ),
FILTER (
Opportunity,
Opportunity[Category] = "Upside"
&& Opportunity[ARR] <> BLANK ()
)
),
,
DESC,
DENSE
),
BLANK ()
)
RETURN
IF ( RANKING2 < 6, RANKING2, BLANK () )
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Best Regards,
Dedmon Dai
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 |
---|---|
110 | |
94 | |
81 | |
66 | |
58 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |