Value by Week Measure KPI

Anonymous · ‎02-22-2021

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.

mahoneypat · ‎02-22-2021

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]
    )