cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
efebo
Frequent Visitor

I need to calculate the percentage of tasks completed on time by each user

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 NameCompletedOnTimeByUserXCompletedOnTimeByUserZ
Task 111
Task 201
Task 311
Task 400
Task 511
% of completion on timeN%N%

 

1 indicates on time and 0 late

 

Thanks in advance. 

1 ACCEPTED SOLUTION
v-juanli-msft
Community Support
Community Support

Hi @efebo 

You could create measures

Capture5.JPG

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.

View solution in original post

4 REPLIES 4
efebo
Frequent Visitor

Thank you very much for both @v-juanli-msft and @ryan_mayu 

v-juanli-msft
Community Support
Community Support

Hi @efebo 

You could create measures

Capture5.JPG

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.

View solution in original post

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.

 

 

ryan_mayu
Super User II
Super User II

@efebo 

 

Is this what you want?

 

Completeontime% = sum('Table'[comeleted on time])/COUNT('Table'[comeleted on time])

1.PNG2.PNG 





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

Proud to be a Super User!




Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Get Ready for Power BI Dev Camp

Power BI Dev Camp - June 24th

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors