cancel
Showing results for
Search instead for
Did you mean:
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"

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

Proud to be a Datanaut !

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

Highlighted
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)```

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

Proud to be a Datanaut !

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

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

## 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 !

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