Calculate the productivity = target time / real time

[Valentin]

Hello everyone 😁

I'm quite a novice in PowerBI so I would like your guidance on my problem please.

I have a list of operations with targeted time and recorded real time in production. Those data are recorded every day for each department of the factory (see example below). 

The goal is to calculate the %productivity = sum(targeted time) / sum(real time) by considering filters of date or department into my dashboard. Can you please give me your advice ? 🤗

Thank ! 

Valentin 

Department	Date	Operations	Target time (hours)	Real time recorded (hours)
Dept 1	29/10/2021	OP1	2	2,5
Dept 1	29/10/2021	OP1	2	4
Dept 1	29/10/2021	OP2	1,5	2
Dept 1	28/10/2021	OP2	1,5	5
Dept 1	28/10/2021	OP3	3	2
Dept 1	28/10/2021	OP3	3	4
Dept 2	29/10/2021	OP4	5	5
Dept 2	29/10/2021	OP4	5	4
Dept 2	29/10/2021	OP5	6	7
Dept 2	28/10/2021	OP5	6	5
Dept 2	28/10/2021	OP6	3	5
Dept 2	28/10/2021	OP6	3	4

[Valentin]

Hi all,

In fact, what I want to have is something to calculate the target time of one OPeration divided by the sum of real time.

I need to take ony once the target time for each operation BUT I need to sum all the real time for each operation. 

Here is an example for the day of 29/10 : 

Productivity = Target time / Sum real time

<=> Productivity = (OP1 Target + OP2 target)/(Sum(OP1 Real Time) + Sum(OP2 Real Time))

<=> Productivity = (2 + 1,5)/((2,5+4)+(2))

<=> Productivity = 3,5/8,5

<=> Productivity = 0,4117...

So that mean ~41% of productivity here. 

Let me know if you have any ideas please... 🙂 

Thank 

Valentin 

[v-kelly-msft]

Hi @Valentin ，

Create 2 dim tables as below:

Date = VALUES('Table'[Date])

Department = VALUES('Table'[Department])

Create relationships.

Then create a measure as below:

Measure =
VAR _Targetbydate =
    IF (
        ISFILTERED ( 'Date'[Date] ),
        CALCULATE (
            SUM ( 'Table'[Target time (hours)] ),
            FILTER (
                ALL ( 'Table' ),
                'Table'[Date]
                    IN FILTERS ( 'Date'[Date] )
                        && 'Table'[Department] = MAX ( 'Table'[Department] )
            )
        )
    )
VAR _Targetbydepartment =
    IF (
        ISFILTERED ( 'Department'[Department] ),
        CALCULATE (
            SUM ( 'Table'[Target time (hours)] ),
            FILTER (
                ALL ( 'Table' ),
                'Table'[Department]
                    IN FILTERS ( Department[Department] )
                        && 'Table'[Date] = MAX ( 'Table'[Date] )
            )
        )
    )
VAR _actualbydate =
    IF (
        ISFILTERED ( 'Date'[Date] ),
        CALCULATE (
            SUM ( 'Table'[Real time recorded (hours)] ),
            FILTER (
                ALL ( 'Table' ),
                'Table'[Date]
                    IN FILTERS ( 'Date'[Date] )
                        && 'Table'[Department] = MAX ( 'Table'[Department] )
            )
        )
    )
VAR _actualbydepartment =
    IF (
        ISFILTERED ( 'Department'[Department] ),
        CALCULATE (
            SUM ( 'Table'[Real time recorded (hours)] ),
            FILTER (
                ALL ( 'Table' ),
                'Table'[Department]
                    IN FILTERS ( Department[Department] )
                        && 'Table'[Date] = MAX ( 'Table'[Date] )
            )
        )
    )
RETURN
    DIVIDE (
        _Targetbydate + _Targetbydepartment,
        _actualbydate + _actualbydepartment
    )

And you will see:

For the related .pbix file,pls see attached.

Best Regards,
Kelly

Did I answer your question? Mark my reply as a solution!

[Valentin]

Hi all,

In fact, what I want to have is something to calculate the target time of one OPeration divided by the sum of real time.

I need to take only once the target time for each operation BUT I need to sum all the real time for each operation. 

Here is an example for the day of 29/10 : 

Productivity = Target time / Sum real time

<=> Productivity = (OP1 Target + OP2 target)/(Sum(OP1 Real Time) + Sum(OP2 Real Time))

<=> Productivity = (2 + 1,5)/((2,5+4)+(2))

<=> Productivity = 3,5/8,5

<=> Productivity = 0,4117...

So that mean ~41% of productivity here. 

Let me know if you have any ideas please... 

Thank 

Valentin 

[v-kelly-msft]

Hi  @Valentin ,

Create a measure as below:

Measure2 =
VAR _tab =
    SUMMARIZE (
        'Table',
        'Table'[Department],
        'Table'[Date],
        [Target time (hours)],
        "real time", SUM ( 'Table'[Real time recorded (hours)] )
    )
VAR _target =
    SUMX (
        FILTER (
            _tab,
            'Table'[Department] = MAX ( 'Table'[Department] )
                && 'Table'[Date] = MAX ( 'Table'[Date] )
        ),
        [Target time (hours)]
    )
VAR _real =
    SUMX (
        FILTER (
            _tab,
            'Table'[Department] = MAX ( 'Table'[Department] )
                && 'Table'[Date] = MAX ( 'Table'[Date] )
        ),
        [real time]
    )
RETURN
    DIVIDE ( _target, _real )

And you will see:

For the related .pbix file,pls see attached.

Best Regards,
Kelly

Did I answer your question? Mark my reply as a solution!

[VahidDM]

Hi @Valentin 

Use DAX to create a measure, try this:

 %productivity = sum(Table[Target time (hours)]) / sum(table[Real time recorded (hours)])

and set the measure format to percentage.

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.

Appreciate your Kudos!!