Forum Discussion

JapDonk89's avatar
JapDonk89
New Member
2 years ago
Solved

SUM issue

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

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    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.

2 Replies

  • audreygerred's avatar
    audreygerred
    Super User
    2 years ago

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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.