Retrieving Column Values When a Measure Reaches Its Maximum

[mh20221111]

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 using Amount2.

1. Retrieve the value of Measure 0 when it reaches its maximum for each category, and obtain the corresponding size at that point.
2. Get the value of Amount1 when the size from step 1 is used (additionally, calculate the difference from Amount1 for each size).
3. Calculate the difference between each size's Amount1 and the value obtained in step 2.

I have accomplished up to step 2, but I am unable to complete step 3. If I can retrieve Amount1 when Measure 0 is at its maximum, that would be sufficient, even if I cannot get the size when Measure 0 reaches its maximum. How can I achieve this?

[v-xinruzhu-msft · Accepted solution]

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.

[mh20221111]

I was able to implement what I wanted to do using the method you explained. Thank you for your guidance.

[mh20221111]

Thank you for your response.
After creating the measure based on the description you provided, I was able to display the desired values and replicate the initial chart using a table visual. However, when I applied a filter on Size, the measure returned 0. I suspect that the issue is related to the use of the ALLSELECTED function. Is there a workaround for this?

[v-xinruzhu-msft]

Hi @mh20221111 

How do you filter the size field? Can you provide some pictures of the problem you encountered?

Best Regards!

Yolo Zhu

[mh20221111]

I'm attaching an image now. Could you please check it?

before filtering
Before

After 

[v-xinruzhu-msft]

Hi @mh20221111 

Please try to replace the allselected() with all()function.

Best Regards!

Yolo Zhu

[Akash_Varuna]

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]

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?