Forum Discussion

klehar's avatar
klehar
Helper V
2 years ago
Solved

Need Target calculation based on running total

Here is my sample data.

I have sorted the running total.

Now I need to get the Target % which is Running Total for each cell/Budget column

Step 2  : I need to compare QoQ at week level if target was met or unmet.

E.g. Week 1 of Q2 compared to Q1 is 12% against 17%. If Week 2 target > Week 2 target of last quarter then Target is Met else Unmet

 

QuarterWeekBudgetActualsRunning TotalTarget %Target Met/Unmet
Q1W1300505017% 
Q1W2300247425% 
Q1W3300138729% 
Q1W43001910635% 
Q1W53002112742% 
Q1W63001113846% 
Q1W73004718562% 
Q1W83002521070% 
Q1W93001022073% 
Q1W103002624682% 
Q1W113002226889% 
Q1W123001528394% 
Q1W1330030313104% 
Q2W1400474712%Unmet
Q2W2400287519%Unmet
Q2W3400219624%Unmet
Q2W44002812431%Unmet
Q2W54002514937%Unmet
Q2W64001616541%Unmet
Q2W74004420952%Unmet
Q2W84003624561%Unmet
Q2W94001325865%Unmet
Q2W104002027870%Unmet
Q2W114002330175%Unmet
Q2W124003733885%Unmet
Q2W134004037895%Unmet

 

How do I write these 2 columns/measures?

Need Help
  • Sahir_Maharaj's avatar
    Sahir_Maharaj
    2 years ago

    Hello klehar,

     

    Can you please try the following:

     

    1. Calculate Target %

    Target % = SalesData[Running Total] / SalesData[Budget]

    2. Determine Target Met/Unmet

    Target Met/Unmet = 
VAR CurrentWeek = SalesData[Week]
VAR CurrentQuarter = SalesData[Quarter]
VAR PreviousQuarterTarget = 
    CALCULATE(
        MAX(SalesData[Target %]),
        FILTER(
            ALL(SalesData),
            SalesData[Week] = CurrentWeek &&
            SalesData[Quarter] = IF(LEFT(CurrentQuarter, 1) = "Q" && LEN(CurrentQuarter) > 1, "Q" & (VALUE(MID(CurrentQuarter, 2, LEN(CurrentQuarter) - 1)) - 1), CurrentQuarter)
        )
    )
RETURN
    IF(
        ISBLANK(PreviousQuarterTarget),
        BLANK(),
        IF(
            SalesData[Target %] > PreviousQuarterTarget,
            "Met",
            "Unmet"
        )
    )

1 Reply

  • Sahir_Maharaj's avatar
    Sahir_Maharaj
    Super User
    2 years ago

    Hello klehar,

     

    Can you please try the following:

     

    1. Calculate Target %

    Target % = SalesData[Running Total] / SalesData[Budget]

    2. Determine Target Met/Unmet

    Target Met/Unmet = 
VAR CurrentWeek = SalesData[Week]
VAR CurrentQuarter = SalesData[Quarter]
VAR PreviousQuarterTarget = 
    CALCULATE(
        MAX(SalesData[Target %]),
        FILTER(
            ALL(SalesData),
            SalesData[Week] = CurrentWeek &&
            SalesData[Quarter] = IF(LEFT(CurrentQuarter, 1) = "Q" && LEN(CurrentQuarter) > 1, "Q" & (VALUE(MID(CurrentQuarter, 2, LEN(CurrentQuarter) - 1)) - 1), CurrentQuarter)
        )
    )
RETURN
    IF(
        ISBLANK(PreviousQuarterTarget),
        BLANK(),
        IF(
            SalesData[Target %] > PreviousQuarterTarget,
            "Met",
            "Unmet"
        )
    )