Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
CJ_96601
Helper V
Helper V

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
PattemManohar
Community Champion
Community Champion

@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 PBI Community Champion




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

 

 

error.  Multiple columns cannot be converted to a scalar value

@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 PBI Community Champion




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

@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 PBI Community Champion




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

 

 

 

@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 PBI Community Champion




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
 

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

image.pngimage.pngimage.png

 





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

Proud to be a PBI Community Champion




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, 

 

 

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

 

 

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





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

Proud to be a PBI Community Champion




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

 

 

Thanks.

 

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

 

 

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

 

image.png





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

Proud to be a PBI Community Champion




Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.