Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Return back max cost value based on ID column

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...
Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    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

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    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