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

85dhruv — Thu, 14 Nov 2019 16:09:07 GMT

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

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

Anonymous — Mon, 18 Nov 2019 11:49:06 GMT

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

topic Re: Tricky measure : Sum at Total not matching the sum of individual row in DAX Commands and Tips

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

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