Return max value if ID is the same - smallest value blank

[Anonymous]

Hi Experts

I want to only return back the max value if the ID's are the same and the value for the ID with the smallest value should be blank. The current measure is returning back max value for both ID's is they are the same....

my Measure

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"))))

[Anonymous · Accepted solution]

Hi  @Anonymous ,

Here are the steps you can follow：

1. Create measure.

Measure =
var _maxvalue=MAXX(FILTER(ALL('QUOTEMAN'),'QUOTEMAN'[IMSTASKID]=MAX('QUOTEMAN'[IMSTASKID])),[OPTTOTALCOST])
var _minvalue=MinX(FILTER(ALL('QUOTEMAN'),'QUOTEMAN'[IMSTASKID]=MAX('QUOTEMAN'[IMSTASKID])),[OPTTOTALCOST])
var _if=
IF(
    MAX('QUOTEMAN'[OPTNCHOICE])=0,
     IF(_maxvalue > 1000000, "VB3",
        IF(_maxvalue >= 100000 && _maxvalue <= 1000000 , "VB2B",
        IF(_maxvalue >= 25000 && _maxvalue <= 100000 , "VB2A","VB1"))))
return
IF(
    MAX('QUOTEMAN'[OPTTOTALCOST])=_maxvalue
    ,
    _if
    ,BLANK()
)

2. Result:

Best Regards,

Liu Yang

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