SUM issue

[JapDonk89]

Hello, 

I am trying to make a sum on overproduction of a certain week.

I have a table with the results of 32 workcenters each day. Planned hours & Produced hours.

Overproduction is what is produced more, than what is planned.

So when on day 1 we planned 8 hours of production, but we produced 10 hours, we have an overproduction of 2 hours. 

When we did not reach the plan, the overproduction is 0.

I am able to create a measure which will show this per work center, see below, but when I want to sum for a full week i get a wrong number.

Per workcenter it should sum the planned hours and sum the production hours to calculate the overproduction hours per work center (below 0 = 0) on the dates in the filter (so could be a day or a week) to get the total sum of overproduction.

hope someone can help with the right formula

[v-zhengdxu-msft · Accepted solution]

Hi @JapDonk89 

Thanks @audreygerred for your concern about this case, please allow me to provide my solution:

First of all, I create a set of sample:

Then add 2 measures:

FDP Week =
IF (
    MAX ( 'Table'[NRM] ) > MAX ( 'Table'[PLN] ),
    1,
    MAX ( 'Table'[NRM] ) / MAX ( 'Table'[PLN] )
)

OP =
IF (
    MAX ( 'Table'[NRM] ) <= MAX ( 'Table'[PLN] ),
    0,
    MAX ( 'Table'[NRM] ) - MAX ( 'Table'[PLN] )
)

Then add a measure to calculate the sum of the OP:

__SUM_OP =
SUMX (
    SELECTCOLUMNS ( 'Table', 'Table'[Work Center], "_OP", 'Table'[OP] ),
    [_OP]
)

The result is as follow:

Best Regards

Zhengdong Xu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[audreygerred]

What is your measure for overproduction? Can you show an example of what you have filtered and what the wrong result is?