How to get the Max of a Summed value based on another Column

[LLP515]

Hi 

Need assistance I am trying to find the Max based on another column although I want the values to be summed depending on the depots filtered.

Example Data

Depot	Date	Time	Value 1	Value 2
CAL	18-Aug	10:00AM	10	0
CAL	18-Aug	11:00AM	10	1
CAL	18-Aug	12:00PM	20	2
CAL	18-Aug	1:00PM	20	9
CAL	18-Aug	2:00PM	15	3
MARC	18-Aug	10:00AM	11	3
MARC	18-Aug	11:00AM	15	4
MARC	18-Aug	12:00PM	21	1
MARC	18-Aug	1:00PM	21	7
MARC	18-Aug	2:00PM	3	8
CAL	19-Aug	10:00AM	10	2
CAL	19-Aug	11:00AM	10	4
CAL	19-Aug	12:00PM	20	1
CAL	19-Aug	1:00PM	20	8
CAL	19-Aug	2:00PM	15	3
MARC	19-Aug	10:00AM	11	2
MARC	19-Aug	11:00AM	15	1
MARC	19-Aug	12:00PM	21	2
MARC	19-Aug	1:00PM	21	5
MARC	19-Aug	2:00PM	3	0

At a total level return Value 2  based on the Max Value 1. These Values should be summed if multiple depots are filtered.

What I am trying to acheive in a matrix with a depot filter

	Date	Time	Value 1	Value 2
	18-Aug	10:00AM	21	3
	18-Aug	11:00AM	25	5
	18-Aug	12:00PM	41	3
	18-Aug	1:00PM	41	16
	18-Aug	2:00PM	18	11
Total	18-Aug		41	16
	19-Aug	10:00AM	21	4
	19-Aug	11:00AM	25	5
	19-Aug	12:00PM	41	3
	19-Aug	1:00PM	41	13
	19-Aug	2:00PM	18	3
Total	19-Aug		41	13

[bhanu_gautam · Accepted solution]

@LLP515 , First create a measure for MAX Value 1

MaxValue1 =

   CALCULATE(

       MAX('Table'[Value 1]),

       ALLEXCEPT('Table', 'Table'[Date], 'Table'[Time])

   )

Then for Corresponding Value 2

CorrespondingValue2 =

   CALCULATE(

       SUMX(

           FILTER(

               'Table',

               'Table'[Value 1] = [MaxValue1] &&

               'Table'[Date] = EARLIER('Table'[Date]) &&

               'Table'[Time] = EARLIER('Table'[Time])

           ),

           'Table'[Value 2]

       ),

       ALLEXCEPT('Table', 'Table'[Date], 'Table'[Time])

   )

Similarly for total value 1 and 2

TotalMaxValue1 =

   CALCULATE(

       MAX('Table'[Value 1]),

       ALLEXCEPT('Table', 'Table'[Date])

   )

 TotalCorrespondingValue2 =

   CALCULATE(

       SUMX(

           FILTER(

               'Table',

               'Table'[Value 1] = [TotalMaxValue1] &&

               'Table'[Date] = EARLIER('Table'[Date])

           ),

           'Table'[Value 2]

       ),

       ALLEXCEPT('Table', 'Table'[Date])

   )

Add a matrix visual to your report.
Drag Date and Time to the rows.
Drag the measures MaxValue1 and CorrespondingValue2 to the values.
To show the totals, ensure that the matrix visual is set to show row totals.

Add a slicer for Depot column

[Anonymous · Accepted solution]

Hi @LLP515 ,

Thanks bhanu_gautam  for the quick reply. I have some other thoughts to add:

(1)We can create measures.

Measure1 = CALCULATE(SUM('Table'[Value 1]),FILTER(ALLSELECTED('Table'),[Date ] in VALUES('Table'[Date ]) && [Time] in VALUES('Table'[Time])))

Value1 = 
var _a=CALCULATE(SUM('Table'[Value 1]),FILTER(ALLSELECTED('Table'),[Date ] in VALUES('Table'[Date ]) && [Time] in VALUES('Table'[Time])))
var _b=MAXX(FILTER(ALLSELECTED('Table'),[Date ] in VALUES('Table'[Date ]) && [Time] in VALUES('Table'[Time])),[Measure1])
var _c=IF(ISINSCOPE('Table'[Time])=FALSE(),_b,_a)
RETURN _c

Value2 = 
var _a=SUM('Table'[Value 2])
var _max=MAXX(FILTER(ALLSELECTED('Table'),[Date ] in VALUES('Table'[Date ]) && [Time] in VALUES('Table'[Time])),[Measure1])
var _b=CALCULATE(MAX('Table'[Time]),FILTER(ALLSELECTED('Table'),[Time] in VALUES('Table'[Time]) && [Date ] in VALUES('Table'[Date ]) && [Measure1]=_max))

var _f=CALCULATE(SUM('Table'[Value 2]),FILTER(ALLSELECTED('Table'),[Date ] in VALUES('Table'[Date ]) && [Time] = _b))
RETURN IF(ISINSCOPE('Table'[Time])=FALSE(),_f,_a)

(2) Then the result is as follows.

Best Regards,

Neeko Tang

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

[Anonymous · Accepted solution]

Hi @LLP515 ,

Thanks bhanu_gautam  for the quick reply. I have some other thoughts to add:

(1)We can create measures.

Measure1 = CALCULATE(SUM('Table'[Value 1]),FILTER(ALLSELECTED('Table'),[Date ] in VALUES('Table'[Date ]) && [Time] in VALUES('Table'[Time])))

Value1 = 
var _a=CALCULATE(SUM('Table'[Value 1]),FILTER(ALLSELECTED('Table'),[Date ] in VALUES('Table'[Date ]) && [Time] in VALUES('Table'[Time])))
var _b=MAXX(FILTER(ALLSELECTED('Table'),[Date ] in VALUES('Table'[Date ]) && [Time] in VALUES('Table'[Time])),[Measure1])
var _c=IF(ISINSCOPE('Table'[Time])=FALSE(),_b,_a)
RETURN _c

Value2 = 
var _a=SUM('Table'[Value 2])
var _max=MAXX(FILTER(ALLSELECTED('Table'),[Date ] in VALUES('Table'[Date ]) && [Time] in VALUES('Table'[Time])),[Measure1])
var _b=CALCULATE(MAX('Table'[Time]),FILTER(ALLSELECTED('Table'),[Time] in VALUES('Table'[Time]) && [Date ] in VALUES('Table'[Date ]) && [Measure1]=_max))

var _f=CALCULATE(SUM('Table'[Value 2]),FILTER(ALLSELECTED('Table'),[Date ] in VALUES('Table'[Date ]) && [Time] = _b))
RETURN IF(ISINSCOPE('Table'[Time])=FALSE(),_f,_a)

(2) Then the result is as follows.

Best Regards,

Neeko Tang

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

[LLP515 · Accepted solution]

Thank you! @Anonymous 

[bhanu_gautam · Accepted solution]

@LLP515 , First create a measure for MAX Value 1

MaxValue1 =

   CALCULATE(

       MAX('Table'[Value 1]),

       ALLEXCEPT('Table', 'Table'[Date], 'Table'[Time])

   )

Then for Corresponding Value 2

CorrespondingValue2 =

   CALCULATE(

       SUMX(

           FILTER(

               'Table',

               'Table'[Value 1] = [MaxValue1] &&

               'Table'[Date] = EARLIER('Table'[Date]) &&

               'Table'[Time] = EARLIER('Table'[Time])

           ),

           'Table'[Value 2]

       ),

       ALLEXCEPT('Table', 'Table'[Date], 'Table'[Time])

   )

Similarly for total value 1 and 2

TotalMaxValue1 =

   CALCULATE(

       MAX('Table'[Value 1]),

       ALLEXCEPT('Table', 'Table'[Date])

   )

 TotalCorrespondingValue2 =

   CALCULATE(

       SUMX(

           FILTER(

               'Table',

               'Table'[Value 1] = [TotalMaxValue1] &&

               'Table'[Date] = EARLIER('Table'[Date])

           ),

           'Table'[Value 2]

       ),

       ALLEXCEPT('Table', 'Table'[Date])

   )

Add a matrix visual to your report.
Drag Date and Time to the rows.
Drag the measures MaxValue1 and CorrespondingValue2 to the values.
To show the totals, ensure that the matrix visual is set to show row totals.

Add a slicer for Depot column