What formula to use to calculate SUM of Number depending on different arguments in the table

[aggiebrown]

Hi all,

I have a table with different measures, trying to create Occupancy so I need to create a new measure that only sums up the AgentStatus Table where Statuses are either "Communicating, Interacting, and On_Queue"

What measure would I use to create this and show at individual metric in Power BI? I am fairly new to DAX.

Sample Data 

[parry2k · Accepted solution]

@aggiebrown you can add a measure like this:

Total = 
CALCULATE ( 
SUM(AgentStatus[DurationSeconds]),
AgentStatus[Status] IN { "Communicating", "Interacting", "On_Queue" }
)

Check my latest blog post Comparing Selected Client With Other Top N Clients | PeryTUS  I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[v-rzhou-msft · Accepted solution]

Hi @aggiebrown 

In addition to parry2k 's reply, you can try sumx function to calculate the result as well. I saw you have built a

"Total Duration Time" measure, sumx function supports you to sum a measure. We can use var function to improve dax performance.

FilterTotal = 
VAR _FilterTable = { "Communicating", "Interacting", "On_Queue" }
VAR _SUM = SUMX(FILTER(AgentStatus,AgentStatus[Status] IN _FilterTable ),[Total Duration Time])
Return
_SUM

Best Regards,

Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. 

[parry2k · Accepted solution]

@aggiebrown you can add a measure like this:

Total = 
CALCULATE ( 
SUM(AgentStatus[DurationSeconds]),
AgentStatus[Status] IN { "Communicating", "Interacting", "On_Queue" }
)

Check my latest blog post Comparing Selected Client With Other Top N Clients | PeryTUS  I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡