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.
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
Quarter | Week | Budget | Actuals | Running Total | Target % | Target Met/Unmet |
Q1 | W1 | 300 | 50 | 50 | 17% | |
Q1 | W2 | 300 | 24 | 74 | 25% | |
Q1 | W3 | 300 | 13 | 87 | 29% | |
Q1 | W4 | 300 | 19 | 106 | 35% | |
Q1 | W5 | 300 | 21 | 127 | 42% | |
Q1 | W6 | 300 | 11 | 138 | 46% | |
Q1 | W7 | 300 | 47 | 185 | 62% | |
Q1 | W8 | 300 | 25 | 210 | 70% | |
Q1 | W9 | 300 | 10 | 220 | 73% | |
Q1 | W10 | 300 | 26 | 246 | 82% | |
Q1 | W11 | 300 | 22 | 268 | 89% | |
Q1 | W12 | 300 | 15 | 283 | 94% | |
Q1 | W13 | 300 | 30 | 313 | 104% | |
Q2 | W1 | 400 | 47 | 47 | 12% | Unmet |
Q2 | W2 | 400 | 28 | 75 | 19% | Unmet |
Q2 | W3 | 400 | 21 | 96 | 24% | Unmet |
Q2 | W4 | 400 | 28 | 124 | 31% | Unmet |
Q2 | W5 | 400 | 25 | 149 | 37% | Unmet |
Q2 | W6 | 400 | 16 | 165 | 41% | Unmet |
Q2 | W7 | 400 | 44 | 209 | 52% | Unmet |
Q2 | W8 | 400 | 36 | 245 | 61% | Unmet |
Q2 | W9 | 400 | 13 | 258 | 65% | Unmet |
Q2 | W10 | 400 | 20 | 278 | 70% | Unmet |
Q2 | W11 | 400 | 23 | 301 | 75% | Unmet |
Q2 | W12 | 400 | 37 | 338 | 85% | Unmet |
Q2 | W13 | 400 | 40 | 378 | 95% | Unmet |
How do I write these 2 columns/measures?
Solved! Go to Solution.
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"
)
)
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"
)
)
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 |
---|---|
105 | |
94 | |
75 | |
63 | |
62 |
User | Count |
---|---|
137 | |
105 | |
104 | |
80 | |
63 |