Tricky measure : Sum at Total not matching the sum of individual row

[85dhruv]

HI ,

I need help with writing a measure where the results are different for row level and total level in a table visualization.

The target is to show the sum of sales for the first 3 years.

Opportunity ID	Year	Sales
1	2019	10
1	2020	20
1	2021	30
1	2022	40
2	2018	50
2	2019	60
2	2020	70
2	2021	80

Needed Result :

Id	First 3 Year sum
1	60	sum of 2019,2020,2021
2	180	sum of 2018,2019,2020
Total	240

But my measure does the calculation of this measure at total level and hence sums up 2018,2019 and 2020 since these are the first 3 years. 

Meausre :

Sales (3yrs) =

var year_start = min('Table'[Year])

Return

calculate(sumx(

FILTER('Table'; 'Table'[Year] <= (year_start + 2) ) ;

'Table'[Sales] )

)

Can you please help me correct this ?

Feedback on formatting welcome too 😛

thanks

[Anonymous]

@85dhruv Please try this measure. In case you want to include more columns in your final table please add those columns in summarize function.

Measure = 
VAR _sales = CALCULATE(SUM('Table'[Sales]),TOPN(3,'Table','Table'[Year],ASC))
VAR _table = SUMMARIZE('Table','Table'[Opportunity ID],"_totalsales",_sales)
RETURN IF(HASONEVALUE('Table'[Opportunity ID]),_sales,SUMX(_table,[_totalsales]))