Using a measure to get the second highest value - complementary suppression

[Anonymous]

Hi All,

I'm in need of creating a measure that will be able to calculate the second highest value (after 5) in a column and then apply it in another measure..

Some context I need to suppress values less than 5 in the display.. however because there is a total showing if there is only 1 value suppressed per a user selection.. the measure needs to then adjust to suprress up to the next smallest value.. (called complimentary suppression)

I have a measure that can verifies that there is only one value being suppressed in the series [Needs Compliment].. However I'm having trouble capturing the next smallest value beyond 5:

Suppression = if(

       [ComplimentNeeded] = "Needs Compliment", //if compliment needed

          if(SUM(TestData[TestNumbers]) < [Next Smallest], "Suppressed With Compliment", //If less than next smallest              SUM(TestData[TestNumbers]), //Otherwise Sum

           if(SUM(TestData[TestNumbers]) < 5, "<5", SUM(TestData[Variable 2]))) //Otherwise apply normal suppression

I've been trying to use the rank function however that gives me literally the second largest rather than the next smallest larger than 5..

Because this all needs to be dynamic to user selection I can't use calculated columns unfortunatley.. 

Any help is hugely appreciated!!

[Anonymous]

Clarification

Rank actually will work.. because if there is only one value suppressed.. therefore requiring compliment.. the next value would be larger than 5..

Here is the measure I'm using to determine the second value: 

NextSmallest = MINX( FILTER(VALUES(TestData[TestNumbers]), RANKX(VALUES(TestData[TestNumbers]), CALCULATE(MAX(TestData[TestNumbers])),,ASC) = 2),

CALCULATE(MAX(TestData[TestNumbers])))

However when I use this formula in the [Suppression] measure originally posted.. it doesn't work because it tries to break the [NextSmallest] measure down see below table example:

As you can see the Next Smallest measure is accuratley calculating the next smallest value is 3.. However I only see it as a total so the Suppression isn't actually doing anything..

[v-piga-msft]

Hi @Anonymous ,

Thank your for your clarification firstly.

I still have a little confused about your requirement.

If it is convenient, could you share your data sample which could reproduce your scenario and your desired output so that we could help further on it.

Best Regards,

Cherry

[Anonymous]

Hi v-piga-msft

I was actually finally able to figure it out.. I appreciate your follow up!

So my goal was to "suppress" the next smallest value in the series when it would otherwise only suppress a single value.. when shown with a total could be easily reverse engineered..

Example series: 10, 4, 7

Suppression Criteria: any value less than 5

So in this case with regular suppression we get: 10, <5, 7.. if shown with the total of 21 it wouldn't take much to figure out what the <5 represents.


Using the Rank function we can apply an asecending order to the series like so:

10 : 3
4   : 1
7   : 2

Next we use an if statement that evaluates when the Rank is "2" checking for if the corresponding value is greater than or less than 5.. If the value is greater than 5 we need complimentary suppression, if it is less than 5 it would be suppressed so we don't need to worry.

Here is the final output:
10,
<5,
Complimentary Suppressed

Now this paired with the total cannot (easily) be reverse engineered for the individual values.

[andinh]

Could you post the dax code? I need to do the same. Thank you