Forum Discussion

arcanri's avatar
arcanri
Frequent Visitor
2 years ago

Return Second Highest Value in Matrix

Hello,    I have calculated the maximum value of each row for a Power BI matrix with the below DAX; my question is: How do I modify it to calculate the 2nd highest value in each row, rather than th...
Need Help
Anonymous's avatar
Anonymous
Not applicable
2 years ago

Thanks for the reply from lbendlin , please allow me to provide another insight:

 

Hi arcanri ,

 

You can try below formula to create measure:

Second_ = 
VAR RankedValues =
    ADDCOLUMNS(
        BidPriceFlat3,
        "Rank", RANKX(
            FILTER(BidPriceFlat3, BidPriceFlat3[DestinationID] = EARLIER(BidPriceFlat3[DestinationID])),
            BidPriceFlat3[NetAfterHaulPerMBF],
            ,
            DESC,
            DENSE
        )
    )
VAR SecondHighestValue =
    CALCULATE(
        MAXX(
            FILTER(RankedValues, [Rank] = 2),
            [NetAfterHaulPerMBF]
        ),
        ALLEXCEPT(BidPriceFlat3, BidPriceFlat3[DestinationID])
    )
RETURN
    SecondHighestValue

 

 

Best Regards,
Adamk Kong

 

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

 

arcanri's avatar
arcanri
Frequent Visitor
2 years ago

Thank you.  I think we're closer to a solution. The above solution returned a ranked list of values; however, it did not return a value of the 2nd highest price.  I have exported out the report into an excel file and modified to mask corporate data.  The yellow highlighted cells are what I am trying to return with the formula.  Thank you again for your help. 

 

Thank you!