Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi,
I'm new to Power BI and I need some help, please.
I have a table of tasks and each one has two parts, part 1 is done by User X and part 2 is done by User Z. That said, I need to calculate the percentage of tasks completed on time by each user.
Task Name | CompletedOnTimeByUserX | CompletedOnTimeByUserZ |
Task 1 | 1 | 1 |
Task 2 | 0 | 1 |
Task 3 | 1 | 1 |
Task 4 | 0 | 0 |
Task 5 | 1 | 1 |
% of completion on time | N% | N% |
1 indicates on time and 0 late
Thanks in advance.
Solved! Go to Solution.
Hi @efebo
You could create measures
Measure X =
VAR p1 =
CALCULATE (
DISTINCTCOUNT ( 'Table 2'[Task Name] ),
FILTER (
ALL ( 'Table 2' ),
'Table 2'[CompletedOnTimeByUserX] = 1
)
)
/ CALCULATE (
DISTINCTCOUNT ( 'Table 2'[Task Name] ),
ALL ( 'Table 2' )
)
RETURN
IF (
ISINSCOPE ( 'Table 2'[Task Name] ),
FORMAT (
SUM ( 'Table 2'[CompletedOnTimeByUserX] ),
"General Number"
),
FORMAT (
p1,
"Percent"
)
)
Measure Y =
VAR p2 =
CALCULATE (
DISTINCTCOUNT ( 'Table 2'[Task Name] ),
FILTER (
ALL ( 'Table 2' ),
'Table 2'[CompletedOnTimeByUserZ] = 1
)
)
/ CALCULATE (
DISTINCTCOUNT ( 'Table 2'[Task Name] ),
ALL ( 'Table 2' )
)
RETURN
IF (
ISINSCOPE ( 'Table 2'[Task Name] ),
FORMAT (
SUM ( 'Table 2'[CompletedOnTimeByUserZ] ),
"General Number"
),
FORMAT (
p2,
"Percent"
)
)
Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @efebo
You could create measures
Measure X =
VAR p1 =
CALCULATE (
DISTINCTCOUNT ( 'Table 2'[Task Name] ),
FILTER (
ALL ( 'Table 2' ),
'Table 2'[CompletedOnTimeByUserX] = 1
)
)
/ CALCULATE (
DISTINCTCOUNT ( 'Table 2'[Task Name] ),
ALL ( 'Table 2' )
)
RETURN
IF (
ISINSCOPE ( 'Table 2'[Task Name] ),
FORMAT (
SUM ( 'Table 2'[CompletedOnTimeByUserX] ),
"General Number"
),
FORMAT (
p1,
"Percent"
)
)
Measure Y =
VAR p2 =
CALCULATE (
DISTINCTCOUNT ( 'Table 2'[Task Name] ),
FILTER (
ALL ( 'Table 2' ),
'Table 2'[CompletedOnTimeByUserZ] = 1
)
)
/ CALCULATE (
DISTINCTCOUNT ( 'Table 2'[Task Name] ),
ALL ( 'Table 2' )
)
RETURN
IF (
ISINSCOPE ( 'Table 2'[Task Name] ),
FORMAT (
SUM ( 'Table 2'[CompletedOnTimeByUserZ] ),
"General Number"
),
FORMAT (
p2,
"Percent"
)
)
Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @v-juanli-msft,
Let's say now I need to calculate the percentage of tasks completed by the department. What do I need to change?
The user wants to know the % completed by department AND user?
Do I need to create another measure?
Can I combine both?
Thanks in advance.
Is this what you want?
Completeontime% = sum('Table'[comeleted on time])/COUNT('Table'[comeleted on time])
Proud to be a Super User!
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.
User | Count |
---|---|
112 | |
97 | |
84 | |
67 | |
60 |
User | Count |
---|---|
150 | |
120 | |
99 | |
87 | |
68 |