Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
MIKAOKSA
Helper I
Helper I

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 displayed individually the numbers are correct but when I try to get to single value the calculations are a bit off. I suspect this has something to do with the "[Osuus työlle %]" not being a sum value but it is a percentage value and I dont think it should be summed, there has to be something else. I am a beginner so please, help eventhough this is propably super simple.
 
Also, how do I insert Pictures on these messages? Would make explaining so much easier.
1 ACCEPTED SOLUTION

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 :

Annotation 2020-02-28 155242.png

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:

 

Annotation 2020-02-28 162120.png

 

Here is my .pbix file.

 

Best Regards,
Kelly
 
Did I answer your question? Mark my post as a solution!

View solution in original post

6 REPLIES 6
Anonymous
Not applicable

Try this
 
Osuuden tavoiteaika =
SUMX('REP_EMPOPERATWR_MV','REP_EMPOPERATWR_MV'[Työn tavoiteaika]) * [Osuus työlle %])
 
Thanks,
PRavin

Hi, 

 

thank you for your quick response. How ever, that did not solve the problem, it gives the exact same answer as before adding that formula.

Situation including work, should add up to 10,74....Situation including work, should add up to 10,74.......but doesnt....but doesnt.

Anonymous
Not applicable

Please share your sample data and expected output.

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. 

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 :

Annotation 2020-02-28 155242.png

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:

 

Annotation 2020-02-28 162120.png

 

Here is my .pbix file.

 

Best Regards,
Kelly
 
Did I answer your question? Mark my post as a solution!

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

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.