Forum Discussion

carl8484's avatar
carl8484
New Member
6 years ago

Convert a SUMIF from Excel

Hi,

 

I'm trying to convert a SUMIF to PowerBI. Someone who could help me in the right direction?

 

I'm having a range of unique CSAT scores in (CSAT[Scores]). Each score represents a customer feedback from a customer after en interaction with a customer service agent. The customer agents ID is a unique number ID listed in (CSAT[UserID]).

 

 

 

I want to connect the score to a name in a new column. So in a separate table I have the unique number ID in [Contacts[UserID] and the name of the agent in [Contacts[Agentname]).

 

 

In Excel, it's quite basic with a SUMIF to check a range for a unique number ID towards a range with corresponding names and summarize results for that agent.

 

What would I like to acheive?

To create a new column in (CSAT) with the actual name of the agent so I can create an average of score, calculate number of responses the agent has received etc. The screenshot below is how it will look like, but currently you will have to know the UserID to find a specific agent from the dropdown "Select advisor" ...

 

 

 

Need Help

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi carl8484 ,

    You can try to CALCULATE and SUM functions or direct use SUMX with FILTER to equivalent excel sumif expression. Since I do not so clear for your data structure, can you please provide some dummy data with excel expression for test?

    In addition, you can also try o use following measure formula if it meets for your requirement:

    result =
VAR selected =
    VALUES ( Selector[Agentname] )
VAR list =
    CALCULATE (
        VALUES ( Contacts[UserID] ),
        FILTER ( ALLSELECTED ( Contacts ), [Agentname] IN selected )
    )
RETURN
    CALCULATE (
        SUM ( CSAT[Scores] ),
        FILTER ( ALLSELECTED ( CSAT ), [UserID] IN list )
    )

    Regards,

    Xiaoxin Sheng