Forum Discussion

MIKAOKSA's avatar
MIKAOKSA
Helper I
6 years ago
Solved

SUM doesnt work

I have a problem in summing calculated values. I have this calculated value:   Osuuden tavoiteaika = SUM('REP_EMPOPERATWR_MV'[Työn tavoiteaika]) * [Osuus työlle %]     When lines are displaye...
Need Help
  • v-kelly-msft's avatar
    v-kelly-msft
    6 years ago

    Hi MIKAOKSA ，

     

    9.58 is the  result of sum(9.67,14.33) * devide( sum(4,73,2.66),sum(5.93,12.59)), as measure is used in context,it will also calculated in the total field, not only simply sum the rows in the column.

    I have 2 suggestions  for you:

    1.convert  the other measures to calculated columns, and you will see :

    2. modify your measure as below:

    Goal time on own part = 
var a = SUMMARIZE('Table (2)','Table (2)'[Index],"A",SUM('Table (2)'[Time on work/Employee])/SUM('Table (2)'[Total completion time of work]),"B",SUM('Table (2)'[Goal time whole work]))
return
SUMX(DISTINCT(a),[A]*[B])

    And you will see:

     

     

    Here is my .pbix file.

     

    Best Regards,
    Kelly
     
    Did I answer your question? Mark my post as a solution!
Anonymous's avatar
Anonymous
Not applicable
6 years ago

Please share your sample data and expected output.

MIKAOKSA's avatar
MIKAOKSA
Helper I
6 years ago
Time on work/EmployeeGoal time on own partPersonal efficiency %EMPIDWORKID% of workGoal time whole workTotal completion time of work
4,737,71163,071T000079,769,675,93
2,663,03113,821T000121,1314,3312,59

 

Goal time whole work, Time on work/employee, total time of work come straight from data.

 

% of work = 

DIVIDE(
    SUM('REP_EMPOPERATWR_MV'[Time on work/employee]);
    SUM('REP_EMPOPERATWR_MV'[Total time of work])
)
 
Goal time on own part =
SUM(REP_EMPOPERATWR_MV[Goal time of whole work]) * [% of work]
 
Personal efficiency =
DIVIDE(
    [Goal time on own part];
    SUM('REP_EMPOPERATWR_MV'[Time on work/Employee])
)
 
Now the problem is when trying to summarize all work together, Goal time on own part should be 10,74 but for some reason it gives 9,58. Time on work/employee for example gives correct result. Its just this column which does not calculate correctly. 
  • v-kelly-msft's avatar
    v-kelly-msft
    Community Support
    6 years ago

    Hi MIKAOKSA ，

     

    9.58 is the  result of sum(9.67,14.33) * devide( sum(4,73,2.66),sum(5.93,12.59)), as measure is used in context,it will also calculated in the total field, not only simply sum the rows in the column.

    I have 2 suggestions  for you:

    1.convert  the other measures to calculated columns, and you will see :

    2. modify your measure as below:

    Goal time on own part = 
var a = SUMMARIZE('Table (2)','Table (2)'[Index],"A",SUM('Table (2)'[Time on work/Employee])/SUM('Table (2)'[Total completion time of work]),"B",SUM('Table (2)'[Goal time whole work]))
return
SUMX(DISTINCT(a),[A]*[B])

    And you will see:

     

     

    Here is my .pbix file.

     

    Best Regards,
    Kelly
     
    Did I answer your question? Mark my post as a solution!
    • MIKAOKSA's avatar
      MIKAOKSA
      Helper I
      6 years ago

      Thank you so much!! 🙂 I did the column thing and it seems to be working perfectly!