Sum Value based on distinct record from other column

[CJ_96601]

Need to sum up values of the largest amount based on badge number and  based on selected year.  Below is the sample table for easy reference.  I appreciate your help

Bold data (the largest of the amount for each badge number)  to be added.

BadgeNo	Amount	Year
1	2000	2018
1	3000	2018
1	1000	2019
1	5000	2018
2	6000	2018
2	8000	2018
2	2000	2019
2	5000	2018
3	2000	2018
4	5000	2018
4	6000	2018
5	5000	2018
Total for  Year 2018  (based on the largest amount per badge number	26000

[PattemManohar]

@CJ_96601  Please create a new table as below and use the Year and Amount field from this new table in your visuals.

Test219Out = SUMMARIZE(Test219MaxSum,Test219MaxSum[Year],Test219MaxSum[BadgeNo],"Amount",MAX(Test219MaxSum[Amount]))

[CJ_96601]

Thank you for your reply.  Can you share the pbix sample file.

[CJ_96601]

error.  Multiple columns cannot be converted to a scalar value

[PattemManohar]

@CJ_96601  Hope you using above DAX to create as a new table. 

[CJ_96601]

I use my table and repace the syntax with the proper column, field.

[PattemManohar]

@CJ_96601 Ok, just for your reference

Test219Out --> NewTable
Test219MaxSum --> Your Source Table or Existing Table

[CJ_96601]

If i understand you correctly, you want me to have a new table?

The data i have shared is just part of the so many records (15000 rows) and for that cannot create a new table.  

Need to use existing table and show the visuals based on my requirements

[PattemManohar]

@CJ_96601  Ok, if you don't want to create an another table (summarized). Then follow the below steps as an alternate method.

Please add a new column to your source table as below

MaxFlag = 
VAR _MaxVal = CALCULATE(MAX(Test219MaxSum[Amount]),FILTER(Test219MaxSum,Test219MaxSum[BadgeNo]=EARLIER(Test219MaxSum[BadgeNo]) && Test219MaxSum[Year] = EARLIER(Test219MaxSum[Year])))
RETURN IF(Test219MaxSum[Amount] = _MaxVal,"Y","N")

Then have a visual level filter as below.

Hope this helps !!

[CJ_96601]

Hi, 

I used this and it works..

SUMX ( DISTINCT( Sheet1[BADGENO] ),CALCULATE ( MAXX (Sheet1,Sheet1[Amount] ) ) )

just need help on how to create systax to link it to the year.

thanks

[PattemManohar]

@CJ_96601  Please update your Measure logic as below and use your Year field as a filter.

Test219 = 
VAR _Total = SUMX(SUMMARIZE(Test219MaxSum,Test219MaxSum[BadgeNo],Test219MaxSum[Year],"Amount",MAX(Test219MaxSum[Amount])),[Amount])
RETURN IF(HASONEFILTER(Test219MaxSum[Year]),SUMX(DISTINCT(Test219MaxSum[BadgeNo]),CALCULATE(MAXX(Test219MaxSum,Test219MaxSum[Amount]))),_Total)

[CJ_96601]

Furthermore,  if i make a relationship (Year), if i click year 2020, the result is zero , null, as there is no record in 2020.  What i would like to have is to have 2019 sum even if i click 2020.

Regards, 

[CJ_96601]

Hi, thank you for your response.

The date (year) i am currently  using  is in another table.

Do i need to make a relationship between the two tables.

As of now, there is only one active relationship in power bi

[PattemManohar]

@CJ_96601 Yes it should have a relationship based on the Year field from which table you want to get it as a filter.

[CJ_96601]

is it possible not having a relationships but create the filter by year (in the measure)?

[CJ_96601]

Thanks.

Shall i add column in Power bi query or in excel file?

[PattemManohar]

@CJ_96601 No, it is in Power Query Editor, once you click on the "Edit Queries"