Help for Newbie -Relationship Database to show relative data

[bengia]

Hello,

I am building a dashboard for HR but i am using multiple reports from Workday to do this.

I want to focus on attrition metrics. Its very easy to build the report to show attriton by region, by quarter.

What i am trying to achieve is actually showing how attrtion is relative to the total number for each region. 

So take a region who has 6,000 active Employees. I want to show the percentage for the quarter of those 6,000 employees who have left,

I have two tables.

Attrition Table (6month view)

Total Workers Table (up to date of total workforce)

both tables have  Employee ID tab.

I followed this, but doesnt seem to work.  I also need the regions to match match too. Both tables have region, but different column names. 

I have watched this video and many others - https://www.youtube.com/watch?v=fVW4MCr0APA

Any help appreciated.

Ben

[v-danhe-msft · Accepted solution]

Hi @bengia,

Based on your description, you could refer to my steps below:

1.I have entered two table data like the picture below:

2.Create three measures to show the Attrition amount, related region amount and the percentage.

Attrition amount = CALCULATE(COUNT('Attrition Table'[Employ Name]),FILTER('Total worker Table','Total worker Table'[Region1]=MIN('Attrition Table'[Region])))

Total account = COUNTX('Total worker Table','Total worker Table'[Region1])

Percentage = [Attrition amount]/[Total account]

3.Create a Table visual and add the related column and create a slicer visual to add the [Quarter] field.

Now you can get the percentage of attrition amount based on the region and you can choose the different quarter by the slicer.

If I misunderstood you, could you offer me some sample data to test for your problem, If the file does contain sensitive data, please upload your report to your OneDrive and send the link to me via private message.

You can also download the PBIX file to have a view.

https://www.dropbox.com/s/cgu1r2n7ofhpojl/Help%20for%20Newbie%20-Relationship%20Database%20to%20show...

Regards,

Daniel He

[bengia]

This is amazing, thanks so much for your help.

Im going to setup some time next week to sort this out

thanks so much again for your guidence. Once ive validated it I will accept the solution.