Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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)))
Solved! Go to Solution.
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
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.