cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
CJ_96601 Regular Visitor
Regular Visitor

Sum Value based on distinct record from other column

 

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.

 

BadgeNoAmountYear
120002018
130002018
110002019
150002018
260002018
280002018
220002019
250002018
320002018
450002018
460002018
550002018
   
Total for  Year 2018  (based on the largest amount per badge number26000 

 

16 REPLIES 16
Super User
Super User

Re: Sum Value based on distinct record from other column

@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]))

image.pngimage.png



Did I answer your question? Mark my post as a solution !

Proud to be a Datanaut !





CJ_96601 Regular Visitor
Regular Visitor

Re: Sum Value based on distinct record from other column

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

 

 

CJ_96601 Regular Visitor
Regular Visitor

Re: Sum Value based on distinct record from other column

error.  Multiple columns cannot be converted to a scalar value

Super User
Super User

Re: Sum Value based on distinct record from other column

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



Did I answer your question? Mark my post as a solution !

Proud to be a Datanaut !





CJ_96601 Regular Visitor
Regular Visitor

Re: Sum Value based on distinct record from other column

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

Super User
Super User

Re: Sum Value based on distinct record from other column

@CJ_96601 Ok, just for your reference

 

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


Did I answer your question? Mark my post as a solution !

Proud to be a Datanaut !





CJ_96601 Regular Visitor
Regular Visitor

Re: Sum Value based on distinct record from other column

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

 

 

 

Super User
Super User

Re: Sum Value based on distinct record from other column

@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.

 

image.pngimage.png

 

Hope this helps !!



Did I answer your question? Mark my post as a solution !

Proud to be a Datanaut !





CJ_96601 Regular Visitor
Regular Visitor

Re: Sum Value based on distinct record from other column

Thanks.

 

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