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 w...
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"
        )
    )
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"
        )
    )