Calculate distinct cumulative percent based on fixed number and filter

[zetzler]

Hello,

I am still very new to Power bi and not sure where to start with my problem.  I am looking to calculate the percent Partipitation over time for a specific team. Our associates submit ideas for improvement and our goal is to have everyone submit an idea by the end of the year.  Our associates are speperated by different production teams and we would like to track by each team which have a fixed value of people in the team.

Example Data:

Date Added	Added By	Team	Comments for this post
1-May-20	ABC	1	First Entry of ABC and should count towards partcipation percent for team 1
2-May-20	ABC	1	ABC should not be counted twice for Team 1
1-May-20	ZYX	2	First Entry of ZYX and should be counted towards partipation percent for team 2

The hope is that using a slicer I can select Team 1 and it will show over time the partipation (Distinct value / Fixed number in team, Each team fixed number is different).

Below is what the chart looked like through Excel Calculations.  You can see the number of Ideas continue to grow through week 3 and 7 but the partcipation percent does not change. this indicates that the same associates were submitting multiple ideas.

[Greg_Deckler]

Right, so seems like there is some missing information, like the size of the teams, you would likely want that in a separate table. Kind of hard to know what you have exactly. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

But, with some assumptions, you should be able to do something like this:

Measure =

  VAR __Count = COUNTROWS(DISTINCT('Table'[Added by]))

  VAR __Total = SUMX('Teams',[Value])

RETURN

  __Count / __Total

If you can do it in Excel, this may help: https://community.powerbi.com/t5/Community-Blog/Excel-to-DAX-Translation/ba-p/1060991

[zetzler]

Thank you for the quick response, I just built a seperate table for the number of employees in each team as you suggested and created a relationship between Table 1 (Opportunity Tracker) and Table 2 (Team Employees).   

I built the chart that I am looking for and the data looks accurate, how do I now chart this so that each months value build upon each other for a cumlative running total.

[Greg_Deckler]

@zetzler  I would suggest starting with the Running Total Quick Measure. Click your table, New Quick Measure.