Forum Discussion
Solved
Sumifs formula to Dax
I'm trying to translate my IF/ Sum IF formula to DAX. I basically want to show the quota attainment for each employee but based on their position. So if they are a manager then the total of the teams attainment should show, not their own. Below is an example and how my SumIfs formula looks. I can get the AE quota to show and but only get as far as showing a simple 1 or 2 if they are a manager/Director
=IF(B3="Rep",F3,IF(B3="Manager",SUMIFS(F:F,C:C,A3),IF(B3="Director",SUMIFS(F:F,D:D,A3),0)))
Hi eyelesszues123, check this. I've also attached pbix file.
Result
Total Attainment = VAR _rep = SELECTEDVALUE('Table'[Rep Name]) VAR _role = SELECTEDVALUE('Table'[Role]) VAR _result = SWITCH( _role, "Rep", [Rep Attainment Total], "Manager", CALCULATE([Rep Attainment Total], KEEPFILTERS('Table'[Manager] = _rep), REMOVEFILTERS()), "Director", CALCULATE([Rep Attainment Total], KEEPFILTERS('Table'[Director] = _rep), REMOVEFILTERS()) ) RETURN _result
3 Replies
- dufoq3
Community Champion
Hi eyelesszues123, check this. I've also attached pbix file.
Result
Total Attainment = VAR _rep = SELECTEDVALUE('Table'[Rep Name]) VAR _role = SELECTEDVALUE('Table'[Role]) VAR _result = SWITCH( _role, "Rep", [Rep Attainment Total], "Manager", CALCULATE([Rep Attainment Total], KEEPFILTERS('Table'[Manager] = _rep), REMOVEFILTERS()), "Director", CALCULATE([Rep Attainment Total], KEEPFILTERS('Table'[Director] = _rep), REMOVEFILTERS()) ) RETURN _result
