Forum Discussion

mh20221111's avatar
mh20221111
Helper II
1 year ago
Solved

Retrieving Column Values When a Measure Reaches Its Maximum

There are two numerical columns, Amount1 and Amount2, along with a category and size as shown in the figure. I will create a measure (Measure 0) that changes based on parameters selected by the user ...
Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    Hi mh20221111 

    You can create a measure.

    Diff =
VAR a =
    SUMMARIZE (
        ALLSELECTED ( table ),
        [Category1],
        [Size],
        [Amount1],
        "Max0", [Measure0]
    )
VAR b =
    MAXX (
        TOPN (
            1,
            FILTER ( a, [Category1] IN VALUES ( table[Category1] ) ),
            [Max0], DESC
        ),
        [Amount1]
    )
RETURN
    SUM ( table[Amount1] ) - b

    Best Regards!

    Yolo Zhu

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

     

Akash_Varuna's avatar
Akash_Varuna
Super User
1 year ago

Hi mh20221111 , Could you please try this please 

  • Create Measure 0 based on user-selected parameters using Amount2.
  • Find Maximum of Measure 0:
    MaxMeasure0 = CALCULATE(MAXX(ALL('Table'[Category]), [Measure 0]))
  • Get Amount1 at Max Measure 0:
    Amount1AtMaxMeasure0 =
    CALCULATE(
    FIRSTNONBLANK('Table'[Amount1], 1),
    FILTER('Table', [Measure 0] = [MaxMeasure0])
    )
  • Calculate the Difference:
    DifferenceFromAmount1 = 'Table'[Amount1] - [Amount1AtMaxMeasure0]
    If this post helped please do give a kudos and accept this as a solution
    Thanks In Advance

 

 

  • mh20221111's avatar
    mh20221111
    Helper II
    1 year ago

    Thank you for your response.

    FIRSTNONBLANK('Table'[Amount1], 1),

    Regarding the above, my table is a calculation table, so I cannot use fully qualified column references. Is there another way to write it?