Total doesnt add up the line values - even when using HASONEVALUE

[robertomari2020]

Hi everyone, 

thanks for this great community! Helped me a lot so far! Before posting this I was reading a loooot of other posts and tried to implement it in the recommended ways but after long time of work and tries i am considering to ask you all here...

I really hope anyone of you can help me with this.

As said in the title my column [To Be Fulfilled Service Jobs (h) uEoY 3] doesnt add up as a sum in the totals. I only want the totals to sum up the row value.

As you can see on the right side, I already have tried a few different things for this column.. without success. Here is one of the 3 tries:

To Be Fulfilled Service Jobs (h) uEoY 3 = 

VAR lineValue   =  [Open Service Jobs (h) uEoY]
                * DIVIDE([To Be Fulfilled Service Jobs (#) uEoY];[Open Service Jobs (#) uEoY])

VAR tempTbl = 
    SUMMARIZE(Tabelle1;Tabelle1[EmployeeID]; 'Calendar'[MonthYear]; "ABCD"; lineValue)
    
RETURN
IF (HASONEVALUE('Calendar'[MonthYear]);
    lineValue;
    SUMX(tempTbl;[ABCD])
) 

Example: 

Month/EmployeeID	To Be Fulfilled Service Jobs (h) uEoY 3	How I would like it
Sep-19 Total	124.7205528	124.78834...
1	75.11334999999998	75.11334999999998
2	49.675040370370375	49.675040370370375

Here is my file:

https://www.dropbox.com/s/q0yflxt6l311zyk/total_not_summing_up_correctly.pbix?dl=0 

Pls ask if you need more infos. I hope I included enough information.

By the way already read some article like these  (but didnt understand how to use in my case):

https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907

(thanks @Greg_Deckler )

https://community.powerbi.com/t5/Community-Blog/Fixing-Total-Errors-In-Power-BI-I-Know-It-Can-Be-Fru...

(thanks @EnterpriseDNA )

and also a lot of answers from @Ashish_Mathur  (thanks also to you)

Anyway... i didnt find an answer after reading and trying. I hope one of you or other members here can help me to solve this 🙂

Thanks!!!!

[Greg_Deckler]

It looks to me that you should not be using EmployeeID in your SUMMARIZE. If I understand what you are going for, you should just need MonthYear.

[robertomari2020]

Hi Greg,

thanks for you really(!) fast answer 🙂

I tried it out like you said, but unfortunately still getting the wrong total... 😞

This is what i tried, but still get the old result:

To Be Fulfilled Service Jobs (h) uEoY 3 = 

VAR lineValue   =  [Open Service Jobs (h) uEoY]
                * DIVIDE([To Be Fulfilled Service Jobs (#) uEoY];[Open Service Jobs (#) uEoY])

VAR tempTbl = 
    SUMMARIZE(Tabelle1; 'Calendar'[MonthYear]; "ABCD"; lineValue)
    
RETURN
IF (HASONEVALUE('Calendar'[MonthYear]);
    lineValue;
    SUMX(tempTbl;[ABCD])
) 

[Greg_Deckler]

Very difficult to troubleshoot these kinds of things. Any way you can share the PBIX? 

Hmm, in taking a closer look at your image, try swapping the order of your EmployeeID and MonthYear in your SUMMARIZE so that it matches your hierarchy in your Rows area of the visual. So summarize by MonthYear and then EmployeeID?

[robertomari2020]

Hi Greg, tried to swap the summarize column order, but also didnt work... 😞

Can you get the file from here? https://www.dropbox.com/s/q0yflxt6l311zyk/total_not_summing_up_correctly.pbix?dl=0 

Thank you for your help and time already!

[Greg_Deckler]

So, if you replace your SUMX with this:

CONCATENATEX(tempTbl,FORMAT([ABCD],"#.##"),",")

You can see that the first three months match the numbers identically and then things start to diverge. I do not know why. I suspect that somewhere within the nest of measures a filter or something else that is critical to the calculation is going awry or being overridden. It is really difficult to say as the measure nesting is formidable.

[robertomari2020]

hmm that could be helpful to trace the error.... but when I replace sumx i get the same value over and over

[robertomari2020]

This problem is killing me... have been trying for days now 😞

[v-yiruan-msft · Accepted solution]

Hi @robertomari2020 ,

Please try to update the format of measure "To Be Fulfilled Service Jobs (h) uEoY 3" as below:

To Be Fulfilled Service Jobs (h) uEoY 3 =
SUMX (
    VALUES ( 'Calendar'[MonthYear] ),
    SUMX (
        VALUES ( 'Tabelle1'[EmployeeID] ),
        [Open Service Jobs (h) uEoY]
            * DIVIDE ( [To Be Fulfilled Service Jobs (#) uEoY], [Open Service Jobs (#) uEoY] )
    )
)

Best Regards

Rena

[Greg_Deckler]

Nice one @v-yiruan-msft !

[robertomari2020]

You are kidding me!

Thanks a lot @v-yiruan-msft !! It really works as expected!!!! You saved my week.

And thanks also @Greg_Deckler for helping me here!