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