Return back max cost value based on ID column

[Anonymous]

Hi Experts

I am trying to return back the max value based on the cost and ID column same table - cannot see my error.

TWC =

VAR _Maxvalue = CALCULATE(MAX(QUOTEMAN[OPTTOTALCOST]), ALLEXCEPT(QUOTEMAN,QUOTEMAN[IMSTASKID]))

VAR _Result =

        IF(QUOTEMAN[OPTNCHOICE] = 0,

        IF(_Maxvalue > 1000000, "VB3",

        IF(_Maxvalue >= 100000 && _Maxvalue <= 1000000 , "VB2B",

        IF(_Maxvalue >= 25000 && _Maxvalue <= 100000 , "VB2A","VB1"))))

I want the lowest ID value to remain blank...

[v-shex-msft · Accepted solution]

Hi @Anonymous,

I modify the formula to use it to calculate the max cost based on the current id group, you can try to use the following measure formula if helps:

TWC =
VAR _Maxvalue =
    CALCULATE (
        MAX ( QUOTEMAN[OPTTOTALCOST] ),
        ALLSELECTED ( QUOTEMAN ),
        VALUES ( QUOTEMAN[IMSTASKID] )
    )
RETURN
    IF (
        MAX ( QUOTEMAN[OPTNCHOICE] ) = 0,
        IF (
            _Maxvalue > 1000000,
            "VB3",
            IF (
                _Maxvalue >= 100000
                    && _Maxvalue <= 1000000,
                "VB2B",
                IF ( _Maxvalue >= 25000 && _Maxvalue <= 100000, "VB2A", "VB1" )
            )
        )
    )

If the above does not help, please share some dummy data that keep the raw table structure to help us clarify your scenario and test.

How to Get Your Question Answered Quickly  

Regards,

Xiaoxin Sheng