cancel
Showing results for
Did you mean:

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.

Anonymous
Not applicable

## Need to create logic of TOP N columns considering multiple columns.

Hi Teams,
Could you please provide solution for below issue:

 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.

Upside_top_weekly_5 =
VAR RANKING2 = IF((Opportunity[Category]= "Upside" && Opportunity[ARR] <> BLANK()), RANKX(FILTER(Opportunity, Opportunity[Category]= "Upside" && Opportunity[ARR] <> BLANK())), Opportunity[ARR] , ,DESC, Dense), BLANK())
RETURN
IF(RANKING2<6, RANKING2, BLANK())

1 ACCEPTED SOLUTION
Community Support

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

2 REPLIES 2
Super User

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

Upside ARR = calculate(SUM(Opportunity[ARR]), KEEPFILTERS(Opportunity[Category]="Upside"))

You could potentially roll this into your ranking measure, but it makes it easy to test the pieces of your calc when it's separate.

Upside_top_weekly_5  =
var currentWeek = SELECTEDVALUE(Opportunity[week start])
VAR RANKING2 = RANKX(FILTER(ALL(Opportunity), Opportunity[Category]= "Upside" && Opportunity[ARR] <> BLANK() && Opportunity[week start] = currentWeek), Opportunity[ARR] , [Upside ARR] ,DESC, Dense)
RETURN
IF(RANKING2<6 && NOT(ISBLANK([Upside ARR])), RANKING2, BLANK())
Community Support

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

Announcements

#### Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

#### Power BI Monthly Update - April 2024

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

#### Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors