Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi Guys,
I have 2 linked tables.
the first has the time in minutes spent for the FTEs for all the services provided:
Time | ||
Services | m | Office |
Service 1 | 206 | Office 3 |
Service 2 | 932 | Office 1 |
Service 3 | 510 | Office 2 |
Service 4 | 331 | Office 1 |
Service 5 | 23 | Office 2 |
Service 6 | 1.225 | Office 1 |
Service 7 | 39 | Office 3 |
… | … | … |
the second table has the total potential of hours worked for each Office:
FTE | |
Office | Potential Hours |
Office 1 | 1200 |
Office 2 | 1500 |
Office 3 | 2500 |
My objective is to understand the difference between potential hours and hours really spent on services.
So I got this formula to calculate the effective hours worked:
= CALCULATE ( SUM ( Time[m] ), FILTER ( FTE, FTE[Office] = EARLIER ( Time[Office] ) ) ) / 60
Now i have to filter out only Service 6 and I am pretty sure there is a smarter way than this:
( CALCULATE ( CALCULATE ( SUM ( Time[m] ), Time[Services] = "Service 1" ), FILTER ( FTE, FTE[Office] = EARLIER ( Time[Office] ) ) ) / 60 ) + ( CALCULATE ( CALCULATE ( SUM ( Time[m] ), Time[Services] = "Service 1" ), FILTER ( FTE, FTE[Office] = EARLIER ( Time[Office] ) ) ) / 60 )Then, when using related function to bring those number on another table, how do I keep Service 6 out of the Job? Should I filter it again manually?
...and so on...
Waiting for your help.
Thanks
Solved! Go to Solution.
You could always add a condition to your calculations which could be: Time[Services]<>"Service 6"
This sets a condition for your calculation, which makes sure it always excludes service 6.
If I misunderstand your challenge, please let me know and elaborate.
Best,
Martin
If you first measure is called "EffectiveHoursWorked" you could filter out Service 6 like this:
No6EffectiveHoursWorked = CALCULATE([EffectiveHoursWworked],FILTER(Time,[Services]<>"Service 6"))
You could always add a condition to your calculations which could be: Time[Services]<>"Service 6"
This sets a condition for your calculation, which makes sure it always excludes service 6.
If I misunderstand your challenge, please let me know and elaborate.
Best,
Martin
Guys,
I you understood and solved my challenge at the same time I feel stupid now though 😄
Thanks
Dear @Greg_Deckler @Anonymous
I am back on my problem...
Now I have gone forward, I have the Operating Cost:
FTE | ||||
Office | Potential Hours | Effective Hours Worked | Operating Cost | Non Operating Cost |
Office 1 | 1.200 | 1.000 | 9.400 | 10.000 |
Office 2 | 1.500 | 1.300 | 9.800 | 7.900 |
Office 3 | 2.500 | 2.000 | 22.000 | 2.900 |
I have to bring them back to the other table:
Time | ||||
Services | m | Office | Operating Cost | Non Operating Cost |
Service 1 | 206 | Office 3 | ? | ? |
Service 2 | 932 | Office 1 | … | … |
Service 3 | 510 | Office 2 | … | … |
Service 4 | 331 | Office 1 | … | … |
Service 5 | 23 | Office 2 | … | … |
Service 6 | 1.225 | Office 1 | 0 | 0 |
Service 7 | 39 | Office 3 | … | … |
… | … | … | … | … |
I have excluded before Service 6 from my calculation, but when bringing it back to the Time table, using the following formula, it will redistribuite also for Service 6.
= RELATED ( FTE[Operating Cost] ) * Time[m] / CALCULATE ( SUM ( Time[m] ), FILTER ( Time, Time[Office] = EARLIER ( Time[Office] ) ) )
I tried different ways but non of them worked out...
Any help?
Thanks
Hi @CiuCiCiao
Did you try to add the condition: Time[Services]<>"Service 6" to your calculation?
= RELATED ( FTE[Operating Cost] ) * Time[m] / CALCULATE ( SUM ( Time[m] ),
Time[Services]<>"Service 6", FILTER ( Time, Time[Office] = EARLIER ( Time[Office] ) ) )
Or something like this?
Best,
Martin
Hi @aidval
Sure I tried, but gives back wrong results! I guess the problem is that I have to filter also the numerator but I can't find a proper way...
Any help?
Thanks
up
Hi @CiuCiCiao
Don't worry I'm going to be at the office in a couple of hour, and I will come up with something for you by then 🙂
@AnonymousI would love you so much!
But since is it is 08 GMT, wherever you are in the world is Saturday, why are you going to the office?
@CiuCiCiao I love my job I run my own business, so I don't really have working hours. I'm always at work. And my girlfriend will join me, so don't worry I am in contact with other people as well
I would very much appreciate either a data set or the pbix. file. That will help me solve your problem 10x faster. Can you do that for me?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
99 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
102 | |
87 | |
64 |