Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
zetzler
New Member

Calculate distinct cumulative percent based on fixed number and filter

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 AddedAdded ByTeamComments for this post
1-May-20ABC1First Entry of ABC and should count towards partcipation percent for team 1
2-May-20ABC1ABC should not be counted twice for Team 1
1-May-20ZYX2First 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.

 
 

 

Participation Percent.PNG

3 REPLIES 3
Greg_Deckler
Super User
Super User

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


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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.

 

Participation Percent Example.PNG

 

 

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


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.