Create a leaderboard based on Dataverse column value

[Kosenurm]

Hi there

I have a pretty basic PowerBI report, which includes completion rates of a certain task within a PowerApp, which is patched to a Dataverse table. 

The columns I am wanting to use here are all the colleagues where action is required by their manager, the action completion status (either 'Completed' or 'Incomplete') and then another column which gives the colleague&leader's Department. 

I would like to create a leaderboard to show, in desending order, who has completed the most tasks (so where the Count of colleagues per department has an action completion status column value of 'Completed'), to try and gamify what is a pretty boring job for our business. 

As a mock up in Excel of what I am trying to achieve in PBI is like this...

All help appreciated

Thanks

K.

[amitchandak]

@Kosenurm , Not very clear on the ask?

If you need a measure it would be like

divide( Countrows(Filter(Table, Table[Status]= "Completed")), Countrows(Table))

[Kosenurm]

Apologies.

A table which shows completion with highest completion percentage first.

Thanks

K

[v-easonf-msft · Accepted solution]

Hi, @Kosenurm 

Try formula as below:

Percent complete =
VAR _completed =
    CALCULATE (
        COUNTROWS ( 'Table' ),
        FILTER (
            ALLEXCEPT ( 'Table', 'Table'[Department] ),
            'Table'[Status] = "Completed"
        )
    )
VAR _total =
    CALCULATE ( COUNTROWS ( 'Table' ), ALLEXCEPT ( 'Table', 'Table'[Department] ) )
RETURN
    _completed / _total

Then apply this filed to a table visual and sort by this field

Best Regards,
Community Support Team _ Eason
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.