Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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 )
(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!!!!
Solved! Go to 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
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.
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])
)
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?
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!
hmm that could be helpful to trace the error.... but when I replace sumx i get the same value over and over
This problem is killing me... have been trying for days now 😞
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
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!
User | Count |
---|---|
84 | |
71 | |
71 | |
67 | |
55 |
User | Count |
---|---|
94 | |
92 | |
91 | |
77 | |
70 |