Calculate sum of maximum value of each country

[chriscorpion786]

Hi All,

I'm trying to calculate the maximum value of each country and then sum the totals.
Can anyone help how to write a measure in Power BI for this. I can do this in Excel with an array formula, but cannot get it in Power BI.
Please see image below: I need to derive the totals which is 1296 of each maximum value for each country. Lets say this table is named Data in BI.

Country	Date	Value		Country	Value
Afghanistan	Wednesday, January 22, 2020	1​		Afghanistan	387​
Afghanistan	Thursday, January 23, 2020	21​		Bahrain	234​
Afghanistan	Thursday, May 21, 2020	45​		Pakistan	675​
Afghanistan	Friday, May 22, 2020	110​		Total	1296​
Afghanistan	Friday, October 2, 2020	234​
Afghanistan	Saturday, October 3, 2020	254​
Afghanistan	Sunday, October 4, 2020	387​
Bahrain	Wednesday, January 22, 2020	0​
Bahrain	Thursday, January 23, 2020	2​
Bahrain	Thursday, May 21, 2020	4​
Bahrain	Friday, May 22, 2020	15​
Bahrain	Friday, October 2, 2020	45​
Bahrain	Saturday, October 3, 2020	119​
Bahrain	Sunday, October 4, 2020	234​
Pakistan	Wednesday, January 22, 2020	112​
Pakistan	Thursday, January 23, 2020	250​
Pakistan	Thursday, May 21, 2020	299​
Pakistan	Friday, May 22, 2020	324​
Pakistan	Friday, October 2, 2020	435​
Pakistan	Saturday, October 3, 2020	555​
Pakistan	Sunday, October 4, 2020	675​

Much appreciated.
Mustafa

 

[sandy666]

without PBI
with PQ group by Country with max by Value then standard Pivot Table just for Total

let
    Source = Excel.CurrentWorkbook(){[Name="Table11"]}[Content],
    Group = Table.Group(Source, {"Country"}, {{"Max", each List.Max([Value]), type number}})
in
    Group

then

Country	MaxValue
Afghanistan	387
Bahrain	234
Pakistan	675
Grand Total	1296

 

[chriscorpion786]

without PBI
with PQ group by Country with max by Value then standard Pivot Table just for Total

I'm actually looking for a DAX measure

 

[sandy666]

Your choice, I prefer easier way ?
good luck

 

[Bo_Ry]

Please try

=sumx(SUMMARIZE(Data,Data[Country],"Max",Max(Data[Value])),[Max])

 

[chriscorpion786]

I'm actually looking for a DAX measure

Thank you Sandy for the Power Query Solution.

 

[chriscorpion786]

Please try

=sumx(SUMMARIZE(Data,Data[Country],"Max",Max(Data[Value])),[Max])

Thank you dear, this is what I was looking for.