[HELP] Compare two datasets to calculate participation rate

[Anonymous]

I have two sets of data. For example:

1. Staff List (1000 staff)

2. Participation list (800 staff)

These two lists have common fields. For example:

1. Staff ID

2. Department

I want to create a 100% stacked bar chart showing participation rates by department, for example:

1. Finance - 80%

2. Operations - 100%

3. Audit - 99%

4. HR - 45%

How do I do this?

[v-robertq-msft · Accepted solution]

Hi, @Anonymous 

According to your description and sample pictures, I can clearly understand what you want to get, I think you can achieve this using measure and clustered bar chart. You can try my steps:

This is my test data based on your sample picture:

1. I created a measure and set the measure format to “Percentage” in the Measure Tool, like this:

Percentage =

var _Countofparticipation=

COUNTX(FILTER('Table',[Staff ID (Participation list)]<>BLANK()),[Staff ID (Participation list)] )

var _Countofall =

COUNTX('Table',[Staff ID (Staff list)])

return

DIVIDE(_Countofparticipation,_Countofall)

2. Then I created a clustered bar chart and place it like this:

And you can get what you want.

You can download my test pbix file here

Best Regards,

Community Support Team _Robert Qin

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[PC2790]

Hi @Anonymous ,

For 100% Stacked bar chart, you would have to select Department in the Axis section and participation rates in the values.

If you don't have the participation rates already, you need to create a measure to calculate it.

The graph would look something like this:

But looking at your expected outcome, I would see you go with Stacked bar chart and change the measure as percentage if not already it is. Please refer the below:

See if it satisfies your requirement.

If you found the answer interesting, please provide a Kudos.

Mark it as solution if it helped you solve your query. 

[Anonymous]

Thanks for the reply.

The confusion Im having is how do I use relationships to tie the unique staff IDs from the two datasets together.

For example:

Based on the example above, I want to get a graph showing:

 How do i do this?