Forum Discussion

Helper V

7 years ago

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

Need Help

PattemManohar

Community Champion

7 years ago

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

Helper V

7 years ago

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
Community Champion
7 years ago
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
Helper V
7 years ago
is it possible not having a relationships but create the filter by year (in the measure)?