Forum Discussion

Mikes128's avatar
Mikes128
New Member
2 years ago
Solved

Help with baseline top 5%

I have the following measures:  top 5 values for the difference between current year and baseline and difference between current year.  The issue I am having is that the top 5 can be +100% due to negative values in the difference.  Is there a way that I can filter the negative values out of the difference measure? 

  • Sahir_Maharaj's avatar
    Sahir_Maharaj
    2 years ago

    Hello Mikes128,

     

    Can you please try the following:

     

    1. Calculate the difference between the current year and the baseline

    DifferenceMeasure = 
    [CurrentYearValue] - [BaselineValue]

    2. Filter out negative values

    FilteredDifferenceMeasure = 
    IF([DifferenceMeasure] > 0, [DifferenceMeasure], BLANK())

    3. Rank the top 5 values of the filtered difference

    RankFilteredDifference =
    RANKX(
        ALL('Table'), 
        [FilteredDifferenceMeasure], 
        , 
        DESC, 
        DENSE
    )

    4. Show the top 5 values of the filtered difference 

    Top5FilteredDifference = 
    IF(
        [RankFilteredDifference] <= 5,
        [FilteredDifferenceMeasure],
        BLANK()
    )

    Hope this helps.

1 Reply

  • Sahir_Maharaj's avatar
    Sahir_Maharaj
    Icon for Super User rankSuper User
    2 years ago

    Hello Mikes128,

     

    Can you please try the following:

     

    1. Calculate the difference between the current year and the baseline

    DifferenceMeasure = 
    [CurrentYearValue] - [BaselineValue]

    2. Filter out negative values

    FilteredDifferenceMeasure = 
    IF([DifferenceMeasure] > 0, [DifferenceMeasure], BLANK())

    3. Rank the top 5 values of the filtered difference

    RankFilteredDifference =
    RANKX(
        ALL('Table'), 
        [FilteredDifferenceMeasure], 
        , 
        DESC, 
        DENSE
    )

    4. Show the top 5 values of the filtered difference 

    Top5FilteredDifference = 
    IF(
        [RankFilteredDifference] <= 5,
        [FilteredDifferenceMeasure],
        BLANK()
    )

    Hope this helps.