How to find Rank 1, Rank 2, Rank 3 highest ROMI

[Anonymous]

Hi All,

I am calculating the First Highest ROMI, Second Highest ROMI, 3rd Highest ROMI etc.

I have created the below DAX measures for the same.

Revenue/Spend (ROMI) = SUM(Spend[revenue]) / SUM(Spend[spend])

Rank 1 ROMI=

VAR RankingRate=VALUES(Spend[campaign_id])

return

CALCULATE([Revenue/Spend (ROMI)],

TOPN(1,VALUES(Spend[campaign_id]),[Revenue/Spend (ROMI)]),RankingRate)

Rank 2 ROMI=

VAR RankingRate=VALUES(Spend[campaign_id])

return

CALCULATE([Revenue/Spend (ROMI)],

TOPN(2,VALUES(Spend[campaign_id]),[Revenue/Spend (ROMI)]),RankingRate)

All these measures are working fine for ROMI calculation.

Now I need to find the Campaign Name for Rank 1,Rank 2, Rank 3 ROMI. But as the campaign name is a text value. The below DAX is not working to find the Campaign name.

Rank 2 Campaign_Name on ROMI=

VAR RankingRate=VALUES(Spend[campaign_id])

return

CALCULATE(MAX(Spend[campaign]),

TOPN(2, VALUES(Spend[campaign_id]),[Revenue/Spend (ROMI)]),RankingRate)

Need a help here.

Thanks in Advance.

[Icey · Accepted solution]

Hi @Anonymous ,

I create a simple example. Please check if it is what you want.

Rank 1 Campaign_Name on ROMI = 
VAR TOPTable =
    TOPN (
        1,
        SUMMARIZE (
            VALUES ( Spend[campaign] ),
            Spend[campaign],
            "ROM", [Revenue/Spend (ROMI)]
        ),
        [Revenue/Spend (ROMI)], DESC
    )
RETURN
    CALCULATE (
        MAX ( Spend[campaign] ),
        FILTER ( TOPTable, [Revenue/Spend (ROMI)] = MINX ( TOPTable, [ROM] ) )
    )

Rank 2 Campaign_Name on ROMI = 
VAR TOPTable =
    TOPN (
        2,
        SUMMARIZE (
            VALUES ( Spend[campaign] ),
            Spend[campaign],
            "ROM", [Revenue/Spend (ROMI)]
        ),
        [Revenue/Spend (ROMI)], DESC
    )
RETURN
    CALCULATE (
        MAX ( Spend[campaign] ),
        FILTER ( TOPTable, [Revenue/Spend (ROMI)] = MINX ( TOPTable, [ROM] ) )
    )

If the rank is repeated, only one campaign_name will be displayed.

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

@Icey Thanks Icey!! That's a great help.

The above calculation is working fine to calculate ROMI(Revenue/Spend) and Campaign_Name based on Rank.

I am trying to achieve the same nth Rank for "VS Last Quarter" . Tried below 2 DAX for the same. None of them is giving the correct result. Getting same values for all the ranks.

Rank 2 VSLastQTR =

MINX (

    TOPN ( 2, VALUES ( Spend[campaign_id] ), [Revenue/Spend (ROMI)], DESC ),

    [VS Last Quarter]

)

Rank2 VSLastQuarter =

VAR

RankingRate=VALUES(Spend[campaign_id])

return

CALCULATE([VS Last Quarter],

TOPN(2,

ALL(Spend[campaign_id]),[Revenue/Spend (ROMI)]),RankingRate)

VS Last Quarter = [Current QTD Revenue/Spend]-[Last QTD Revenue/Spend] 

Current QTD Revenue/Spend =

    CALCULATE([Revenue/Spend (ROMI)],DATESQTD('Spend Reported Date'[Date]))

Last QTD Revenue/Spend =

    CALCULATE([Revenue/Spend (ROMI)],DATESQTD(DATEADD('Spend Reported Date'[Date],-1,QUARTER)))

For Rank 2,Rank 3, Rank 4 "VS Last Quarter" values are coming same. Not working as expected.

Do not know where am I going wrong.

Thanks In Advance.

[Icey]

Hi @Anonymous ,

What about this?

Rank 2 VSLastQTR =
MINX (
    TOPN ( 2, VALUES ( Spend[campaign_id] ), [VS Last Quarter], DESC ),
    [VS Last Quarter]
)

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[parry2k]

@Anonymous you can use MAX function for Campign Name instead of value, change it in all measures

Rank 1 ROMI= 
VAR RankingRate=VALUES(Spend[campaign_id])
return
CALCULATE(MAX( Spend[campaign_name]),
TOPN(1,VALUES(Spend[campaign_id]),[Revenue/Spend (ROMI)]),RankingRate)

[Anonymous]

@parry2k  Hi Parry,

Yes I am doing the same . It is working fine for First Rank. But for 2nd Rand onwards it is not showing me the correct Campaign Name.

I have to find the Campaign name till 6th Rank ROMI.

Below is my DAX for Second  Highest ROMI  Campaign Name:

Rank 2 Campaign_Name on ROMI=

VAR RankingRate=VALUES(Spend[campaign_id])

return

CALCULATE(MAX(Spend[campaign]),

TOPN(2, VALUES(Spend[campaign_id]),[Revenue/Spend (ROMI)]),RankingRate)