Forum Discussion

JKross's avatar
JKross
Icon for Helper I rankHelper I
1 year ago
Solved

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        
YEAR2024202420242024202420242024…
CW1234567…
Team12.9633.5123.2123.3864.7262.5842.509 
Team23.9504.5153.7063.9274.4403.1463.240 
Team35.4324.5155.4366.2295.1565.5054.704 
TOTAL WL12.34512.54312.35413.54214.32111.23410.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        
YEAR2024202420242024202420242024…
CW1234567…
Team124%28%26%25%33%23%24% 
Team232%36%30%29%31%28%31% 
Team344%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 YEAR2025202520252025202520252025…
Target CW1234567…
Team1To calculate as Total X %WL_perTeam_CW Last Year 
Team2 
Team3 
TOTAL TARGET15.00015.00015.00015.00015.00015.00015.000 



Any ideas?

  • Anonymous's avatar
    Anonymous
    1 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

  • 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 * SharePY

     

    Check the above two modified one, hope thishelps.

  • 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.


  • Anonymous's avatar
    Anonymous
    Not 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

  • 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!