Jun 09 2021 01:41 PM
Hello,
How can I show only the MAX values in the attached? The pivot is also showing the other dates that are not the MAX. Kindly see attached. This is simplified example and my dataset is much larger.
thank you.
Jun 09 2021 02:00 PM
You cannot do that with a pivot table.
I have attached a version with array formulas that work in all versions of Excel.
If you have Microsoft 365, simpler formulas are possible, and Power Query would be another option.
Jun 09 2021 02:14 PM
If you are not on Mac, creating PivotTable add data to data model
and add DAX measure
Latest Amt=
VAR maxAmt=MAX(Table1[Amt])
VAR maxAmtCo=CALCULATE(MAX(Table1[Amt]), ALL(Table1[Date]))
RETURN
IF(maxAmt=maxAmtCo, maxAmt, BLANK() )
as
and use it in values
Jun 09 2021 02:52 PM
Jun 09 2021 06:46 PM
Jun 10 2021 03:22 PM
SolutionNot sure I understood what exactly do you mean in first question, how do you create measures? In general it could be as many measures as you wish
To check earliest date with max amount for the company we may use something like
Max Amt:=
VAR biggestAmount =
MAX ( Table1[Amt] )
VAR earliestDate =
MIN ( Table1[Date] )
VAR maxAmt =
CALCULATE (
MAX ( Table1[Amt] ),
ALL ( Table1[Date] )
)
VAR minDate =
CALCULATE (
MIN ( Table1[Date] ),
ALL ( Table1[Date] ),
Table1[Amt] = maxAmt
)
RETURN
IF (
biggestAmount = maxAmt
&& minDate = earliestDate,
biggestAmount,
BLANK ()
)
Please check attached.
Jun 12 2021 10:11 AM
Jun 10 2021 03:22 PM
SolutionNot sure I understood what exactly do you mean in first question, how do you create measures? In general it could be as many measures as you wish
To check earliest date with max amount for the company we may use something like
Max Amt:=
VAR biggestAmount =
MAX ( Table1[Amt] )
VAR earliestDate =
MIN ( Table1[Date] )
VAR maxAmt =
CALCULATE (
MAX ( Table1[Amt] ),
ALL ( Table1[Date] )
)
VAR minDate =
CALCULATE (
MIN ( Table1[Date] ),
ALL ( Table1[Date] ),
Table1[Amt] = maxAmt
)
RETURN
IF (
biggestAmount = maxAmt
&& minDate = earliestDate,
biggestAmount,
BLANK ()
)
Please check attached.