Forum Discussion

eyelesszues123's avatar
eyelesszues123
New Member
2 years ago
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...
Need Help
  • dufoq3's avatar
    dufoq3
    2 years ago

    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

     

dufoq3's avatar
dufoq3
Community Champion
2 years ago

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

 

  • dufoq3's avatar
    dufoq3
    Community Champion
    2 years ago

    You're welcome