Forum Discussion
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 using Amount2.
- Retrieve the value of Measure 0 when it reaches its maximum for each category, and obtain the corresponding size at that point.
- Get the value of Amount1 when the size from step 1 is used (additionally, calculate the difference from Amount1 for each size).
- 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?
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] ) - bBest Regards!
Yolo Zhu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
8 Replies
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] ) - bBest Regards!
Yolo Zhu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
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?Hi mh20221111
How do you filter the size field? Can you provide some pictures of the problem you encountered?
Best Regards!
Yolo Zhu
I was able to implement what I wanted to do using the method you explained. Thank you for your guidance.
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
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?
