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.
Hi all,
I believe this dilemma is solvable by DAX, but I don't have enough experience to solve it myself.
We have data that comes in by date, item, and scheduled and produced amounts. If an item is over or under-produced, it does not meet the KPI (between 95% and 105%).
In simplest terms, I need a measure which will compare the number of items which made KPI by week by the total number of items worked on by week.
Here is a sample table:
Item | 123A | 123B | 123C | |||
Week | Production Accuracy | KPI Met | Prod. Acc. | KPI Met | Prod. Acc. | KPI Met |
1 | 94% | No (0) | 90% | No (0) | 101% | Yes (1) |
2 | 104% | Yes (1) | 95% | Yes (1) | 102% | Yes (1) |
3 | 99% | Yes (1) | 103% | Yes (1) | ||
Total | 99% | 66% (2/3) | 92.5% | 50% (1/2) | 102% | 100% (3/3) |
Total KPI Met for above table: 6 met over 8 overall, 6/8, 75%.
Getting the production accuracy measure was pretty simple, but getting the KPI Met measure has proved tricky since it has to remain consistent when being rolled up to month, year, and a lot of different categories (work station, item category, etc).
If more clarification is needed please let me know. Thank you.
It would be better to share your example table and an example of correct results. Here is a measure expression pattern that may work.
Avg Weekly KP Met =
VAR vSummary =
ADDCOLUMNS (
DISTINCT ( Date[Week] ),
"cCount",
CALCULATE (
COUNTROWS ( Table )
),
"cKPIMet",
CALCULATE (
COUNTROWS ( Table ),
FILTER (
ALL (
Table[Scheduled],
Table[Produced]
),
Table[Produced] >= 0.95 * Table[Scheduled]
&& Table[Produced] <= 1.05 * Table[Scheduled]
)
)
)
RETURN
AVERAGEX (
vSummary,
[cKPIMet] / [cCount]
)
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
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 |
---|---|
37 | |
25 | |
19 | |
14 | |
8 |
User | Count |
---|---|
69 | |
48 | |
44 | |
18 | |
15 |