Forum Discussion

v-kelly-msft's avatar
v-kelly-msft
Icon for Community Support rankCommunity Support
4 years ago

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's avatar
Valentin
New Member
4 years ago

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's avatar
    v-kelly-msft
    Icon for Community Support rankCommunity Support
    4 years ago

    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!