SOLVED: RANKX if more than column value

[Anonymous]

Hi,

I want to rank campaigns based on their clickthrough rate but I would only want to rank those with total emails delivered of over 200. 

Here's my DAX: 

Rank Campaign by CTR = 
    IF(
        [Total Emails Delivered]>=200,
        RANKX(ALLSELECTED('Email Analysis'[Campaign Name]),[Clickthrough Rate],,DESC),BLANK())

But in my table, it's seems to start the rank at 2 or 3:

Any way to fix this?

[Anonymous]

Hi @Anonymous 

Try this measure.

Rank Campaign by CTR =
IF (
    HASONEVALUE ( 'Email Analysis'[Campaign Name] ),
    IF (
        [Total Emails Delivered] > 200,
        RANKX (
            FILTER (
                ALL ( 'Email Analysis' ),
                'Email Analysis'[Email Group] = MAX ( 'Email Analysis'[Email Group] )
                    && [Total Emails Delivered] > 200
            ),
            [Click through.Rate],
            ,
            DESC
        ),
        BLANK ()
    ),
    BLANK ()
)

My result in my sample is as below.

Best Regards,
Rico Zhou

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

[Anonymous · Accepted solution]

Hi Rico,

Thanks for this.

I've managed to solve the issue with this measure:

    IF([Total Emails Delivered] >= 200,
            RANKX(FILTER(
                ALLSELECTED('Campaign and Group Slicer'[Campaign Name]),[Total Emails Delivered]>=200),[Clickthrough Rate]))

[Ashish_Mathur]

Hi,

Share the link from where i can download your PBI file.

[Anonymous]

Hi @Ashish_Mathur,

Unfortunately due to my company's policy, I'm unable to share the files with you.

Is there a workaround where you can still help me?

[Greg_Deckler]

@Anonymous Maybe:

Rank Campaign by CTR = 
        RANKX(ALLSELECTED('Email Analysis'[Campaign Name]),[Total Emails Delivered]>=200,[Clickthrough Rate],DESC)

[Anonymous]

Hey @Greg_Deckler,

Unfortunately this didn't solve it. Here's the outcome:

[Greg_Deckler]

@Anonymous Can you post what is displayed in See details? Also, even better, can you post sample data? Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.

[Anonymous]

Hi @Greg_Deckler,

Thanks for the tips!

Fairly new to Power Bi and the community so really appreciate the help.