Forum Discussion
Calculating Current Year Team Targets based on Past Years Team Shares in a constant way
Hi,
I hope somebody can help.
In PowerBI I want to calculate the Current Years Target per Team and Calendar Week. The weekly Total Target is given. The Share per Team shall be as of Last Year and can be derived from the Actuals.
I have calculated this already in several measures in my Model, but I am facing two Problems:
a) In case I add a Slicer to the Teams, the Share for the Team gets recalculated to 100%
b) The resulting Targets are different to when I use a Martix Visual (Incorrect Shares) to a Stacked Column Visual (Correct Shares), whereas in both visuals all Slicers, filters, Measures etc. are identical.
How can I fix this and how can I calculate once and constant the Share per Teams, based on Last Year's Actual WorkLoad?
Here is what I have done:
1. Workload per Team and Calendarweek:
WL_perTeam_CW=
IF(SELECTEDVALUE('SD Staff [Teams] IN Values('Selected Teams[Sel.Teams])
SUMX(
VALUES('SD Staff [Teams]);
CALCULATE(
COUNTROWS('DATA');
'DATA'[CloseDate]<>Blank();
'SD Staff '[StaffID]=MAX('DATA'[StaffID]);
'SD Staff'[Valid From]<=Max('DATA'[CloseDate]);
'SD Staff'[ValidTo]=Max('DATA'[CloseDate])
)
);
0)
Idea: To have these type of data (works as well in visuals):
| WL_perTeam_CW | ||||||||
| YEAR | 2024 | 2024 | 2024 | 2024 | 2024 | 2024 | 2024 | … |
| CW | 1 | 2 | 3 | 4 | 5 | 6 | 7 | … |
| Team1 | 2.963 | 3.512 | 3.212 | 3.386 | 4.726 | 2.584 | 2.509 | |
| Team2 | 3.950 | 4.515 | 3.706 | 3.927 | 4.440 | 3.146 | 3.240 | |
| Team3 | 5.432 | 4.515 | 5.436 | 6.229 | 5.156 | 5.505 | 4.704 | |
| TOTAL WL | 12.345 | 12.543 | 12.354 | 13.542 | 14.321 | 11.234 | 10.453 | |
| // StaffID's changed Teams, datacount needs to check in which Team the StaffID was at CloseDate | ||||||||
| // more StaffID's and Teams helped out, with the Workload, but irrelevant for future Projection, therefor calculation only for Selected Teams |
2. Workload Share per Team and Calendarweek:
%WL_perTeam_CW=
IF(SELECTEDVALUE('SD Staff'[Teams]) IN Values('Selected Teams[Sel.Teams])
DIVIDE(
[WL_perTeamCW];
CALCULATE(
COUNTROWS('DATA');
'SD Staff'[Teams IN Values('Selected Teams[Sel.Teams])
'DATA'[CloseDate]<>Blank()
);
0);
0)
Idea to have this type of data (this does not work with Slicers on visuals and gives different shares in Stacked column Visual to Matrix Visual)
| %WL_perTeam_CW | ||||||||
| YEAR | 2024 | 2024 | 2024 | 2024 | 2024 | 2024 | 2024 | … |
| CW | 1 | 2 | 3 | 4 | 5 | 6 | 7 | … |
| Team1 | 24% | 28% | 26% | 25% | 33% | 23% | 24% | |
| Team2 | 32% | 36% | 30% | 29% | 31% | 28% | 31% | |
| Team3 | 44% | 36% | 44% | 46% | 36% | 49% | 45% | |
| TOTAL WL in % | 100% | 100% | 100% | 100% | 100% | 100% | 100% |
3. WorkLoad Share per Team Last year
%WL_perTeam_CW_PY =
CALCULATE([%WL_perTeam_CW];
SAMEPERIODLASTYEAR('Calendar IN'[IN Date])
)
4. Target Workload per Team and Calendarweek for the Current Year:
Target_PerTeam_CW =
VAR CWPlan = MAX(Target'[CW])
VAR Teams = SELECTEDVALUE('SD Staff'[Teams])
VAR SharePY = [WL_perTeam_CW_PY]
VAR Target_perWeek =
CALCULATE(
MAX('Target'[Total Target per CW]);
'Calendar IN'[IN CW]=CWPlan)
RETURN
Target_perWeek*SharePY
The Idea is to get this type of Data - and being able to add slicers on Teams. Currently when I slice or filter one Team, the Target gets recalculates to the Total Target of 15.000. :
| Target_PerTeam_CW | ||||||||
| Target YEAR | 2025 | 2025 | 2025 | 2025 | 2025 | 2025 | 2025 | … |
| Target CW | 1 | 2 | 3 | 4 | 5 | 6 | 7 | … |
| Team1 | To calculate as Total X %WL_perTeam_CW Last Year | |||||||
| Team2 | ||||||||
| Team3 | ||||||||
| TOTAL TARGET | 15.000 | 15.000 | 15.000 | 15.000 | 15.000 | 15.000 | 15.000 |
Any ideas?
- Anonymous1 year ago
Hi JKross ,
Did the above suggestions help with your scenario? if that is the case, you can consider Kudo or Accept the helpful suggestions to help others who faced similar requirements.
If these also don't help, please share more detailed information and description to help us clarify your scenario to test.
How to Get Your Question Answered Quickly
Regards,
Xiaoxin Sheng
4 Replies
- rajendraongole1
Super User
Hi JKross - we need to ignore the Team filter in the calculation of %WL_perTeam_CW_PY. You can achieve this using REMOVEFILTERS in DAX.
when you select a single Team in the slicer, the proportion remains as it was in the previous year.
%WL_perTeam_CW_PY =
VAR TotalWL_PY =
CALCULATE(
[WL_perTeam_CW],
SAMEPERIODLASTYEAR('Calendar IN'[IN Date]),
REMOVEFILTERS('SD Staff'[Teams]) // Ignore the slicer on Teams
)
VAR TeamWL_PY =
CALCULATE(
[WL_perTeam_CW],
SAMEPERIODLASTYEAR('Calendar IN'[IN Date])
)
RETURN
DIVIDE(TeamWL_PY, TotalWL_PY, 0)Now, update Target_PerTeam_CW to use the corrected %WL_perTeam_CW_PY:
Target_PerTeam_CW =
VAR CWPlan = MAX('Target'[CW])
VAR SharePY =
CALCULATE(
[%WL_perTeam_CW_PY],
REMOVEFILTERS('SD Staff'[Teams]) // Ensures that shares remain constant
)
VAR Target_perWeek =
CALCULATE(
MAX('Target'[Total Target per CW]),
'Calendar IN'[IN CW] = CWPlan
)
RETURN
Target_perWeek * SharePYCheck the above two modified one, hope thishelps.
- JKross
Helper I
HI rajendraongole1 ;
thank you for replying so quickly. I tried it but the results looked rather wild, though it is possible, I am a little "Daxed out" for today, I will retry with a fresh head next week.
When I changed %WL_perTeam_CW_PY and Target_PerTeam_CW as you described, now the Targets are calculated for all Teams in the Model (Team 4 to Team 😎 and all with a constant share.
Is it possible that REMOVEFILTER, removes as well the DAX where I specify to calculate the Workload and the Share only for Selected Teams:SELECTEDVALUE('SD Staff'[Teams]) IN Values('Selected Teams[Sel.Teams])?
I will try next week with a freshhead and give notice. - AnonymousNot applicable
Hi JKross ,
Did the above suggestions help with your scenario? if that is the case, you can consider Kudo or Accept the helpful suggestions to help others who faced similar requirements.
If these also don't help, please share more detailed information and description to help us clarify your scenario to test.
How to Get Your Question Answered Quickly
Regards,
Xiaoxin Sheng
- JKross
Helper I
HI Anonymous ; yes it seems it all worked out - still testing.
I made it somewhat simpler and inserted the REMOVEFILTERS in the Calculation of the weekly workload per Team directly. So it's only at one place and seems to work out fine!