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

[Anonymous]

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())

[d_gosbell]

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.

Then I adjusted your ranking measure as follows:

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())

[v-deddai1-msft · Accepted 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