Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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 |
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
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!
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
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!
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!!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.