Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Weighted Goal Based on Different Scores

Hey Guys. Need help getting the same results in Powerbi. I'm trying to calculate the Weighted Average Goal base on the MTD results or Weekly Results. In excel i Use  =SUMPRODUCT(C3:C11,D3:D11)/SUM(D3:D11) , but want to build the same in PBI


 

Thanks 

 

Need Help
  • amitchandak's avatar
    amitchandak
    6 years ago

    This will work only when sum goal and Response # are columns. Or we have move calculation inside sumx

    Or we need to summarize data at line level

    AverageX(summarize(table,table[agent],table[tenaure],"_goal"
		,sumx(table,table[client goal Minimum]*table[Tasks Handled]),"_task",SUM(table[Tasks Handled])
	),divide([_goal]/[_task]))

     

3 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    6 years ago

    Try

    divide(SUMX(table,table[client goal Minimum]*table[Tasks Handled]),SUM(table[Tasks Handled]))

     

    Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution.
    In case it does not help, please provide additional information and mark me with @
    Thanks. My Recent Blogs -Decoding Direct Query - Time Intelligence, Winner Coloring on MAP, HR Analytics, Power BI Working with Non-Standard TimeAnd Comparing Data Across Date Ranges
    Connect on Linkedin

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Thanks for the prompt response.  im getting 485 instead of the 66.6 which is the weighted goal im getting from my excel. In this project im using a table called goals where I have the following. 

       

      Channel       Goal

      Cx Chat55.00%
      Cx Phone45.00%
      Cx Web45.00%
      Dx Chat80.00%
      Dx Phone70.00%
      Dx Web50.00%
      Mx Chat55.00%
      Mx Phone60.00%
      Mx Web25.00%

       

      I have to measure response, since I could use the original table CSAT by Agent where Total responses is located

       

      • amitchandak's avatar
        amitchandak
        Icon for Super User rankSuper User
        6 years ago

        This will work only when sum goal and Response # are columns. Or we have move calculation inside sumx

        Or we need to summarize data at line level

        AverageX(summarize(table,table[agent],table[tenaure],"_goal"
		,sumx(table,table[client goal Minimum]*table[Tasks Handled]),"_task",SUM(table[Tasks Handled])
	),divide([_goal]/[_task]))