Forum Discussion

Kosenurm's avatar
Kosenurm
Regular Visitor
4 years ago
Solved

Create a leaderboard based on Dataverse column value

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.

Need Help
Tips and Tricks
  • v-easonf-msft's avatar
    v-easonf-msft
    4 years ago

    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.

3 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    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's avatar
      Kosenurm
      Regular Visitor
      4 years ago

      Apologies.

       

      A table which shows completion with highest completion percentage first.

       

      Thanks

      K

      • v-easonf-msft's avatar
        v-easonf-msft
        Community Support
        4 years ago

        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.