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.
|Total for Year 2018 (based on the largest amount per badge number||26000|
@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]))
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.
Hope this helps !!