cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Super User
Super User

Re: Sum Value based on distinct record from other column

@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 Datanaut !





CJ_96601 Regular Visitor
Regular Visitor

Re: Sum Value based on distinct record from other column

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
 
Super User
Super User

Re: Sum Value based on distinct record from other column

@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 Datanaut !





CJ_96601 Regular Visitor
Regular Visitor

Re: Sum Value based on distinct record from other column

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 Regular Visitor
Regular Visitor

Re: Sum Value based on distinct record from other column

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, 

 

 

Super User
Super User

Re: Sum Value based on distinct record from other column

@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 Datanaut !





Highlighted
CJ_96601 Regular Visitor
Regular Visitor

Re: Sum Value based on distinct record from other column

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