Create a measure from columns in different tables

[almaarapi]

Hello, 

I have two tables related to each other. One has recorded employee time, broken down by time type and the other planned time (planeed time is daily not dependent on time type)

I want to create a measure with % of recorded time/planned time. I cna do this at total level however, I am not able to make it work in a chart where the employee name is in the axis and time type in the labels. 

Could someone suggest a solution?

Recordings Table					Planned Time Table
Date	Employee Code	Time Type	Recorded Time		Date	Employee Code	Planned Time
01/01/2021	3445	a	4			01/01/2021	3445	8
01/01/2021	3445	v	5			02/01/2021	3445	8
02/01/2021	3445	d	2			01/01/2021	4556	7
01/01/2021	4556	d	4			02/01/2021	4556	7
01/01/2021	4566	q	2			01/01/2021	2222	5
02/01/2021	4566	c	4			02/01/2021	2222	5
01/01/2021	2222	a	4
01/01/2021	2222	d	1
02/01/2021	2222	q	3

[almaarapi]

Hi Jihwan, 

I made up the data just for the sake of showing what I was trying ot achieve. The data I have go back in time for months so I need to be able to slice them properly. The formula you sent were well for the total timeframe but once I put slicer on the report the filtering did not work correctly as it filters the recording time but the planned time remains the total. A better example would be below. 

Thanks

Recordings Table					Planned Time Table
Date	Employee Code	Time Type	Recorded Time		Date	Employee Code	Planned Time
01/01/2021	3445	a	4			01/01/2021	3445	8
01/01/2021	3445	v	5			02/01/2021	3445	8
02/01/2021	3445	d	2			01/01/2021	4556	7
01/01/2021	4556	d	4			02/01/2021	4556	7
01/01/2021	4566	q	2			01/01/2021	2222	5
02/01/2021	4566	c	4			02/01/2021	2222	5
01/01/2021	2222	a	4			01/03/2021	3445	8
01/01/2021	2222	d	1			02/03/2021	3445	8
02/01/2021	2222	q	3			01/03/2021	4556	7
01/03/2021	3445	a	2			02/03/2021	4556	7
01/03/2021	3445	v	4			01/03/2021	2222	5
02/03/2021	3445	d	7			02/03/2021	2222	5
01/03/2021	4556	d	3
01/03/2021	4566	q	4
02/03/2021	4566	c	5
01/03/2021	2222	a	7
01/03/2021	2222	d	1
02/03/2021	2222	q	3

[Jihwan_Kim]

Hi,

Thank you for your feedback.

I don't know about your data model, but I think you can connect the two tables.

As you have seen in my sample pbix file, there was no connection between the two tables.

Thank you.

[almaarapi]

Hi Juhwan, 

That is what I was looking for but I cannot make it work with time Slicers. It works on totals but if I set a slicer for Month 3 or Week 12 the % calculated is still towards total planned not total in the selected timeframe. 

Thanks

[Jihwan_Kim]

Hi,

Thank you for your feedback, but I do not understand setting a slicer for Month3. Because I could not find it in your sample.

I assume you have a different sample.

[Jihwan_Kim]

Hi, @almaarapi 

Please correct me if I wrongly understood your question.

I am not sure about how you want to describe the result. Please check the below picture and the sample pbix file's link down below, whether it is what you are looking for.

DAX measure is in the sample pbix file.

https://www.dropbox.com/s/eureyhe950g5rbr/almaarapi.pbix?dl=0 

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

[almaarapi]

Hi Jihwan, 

thanks. The problem with this is that is doing the calcualtion on specifc date. e.g. employee code 2222, time type q is 60% in the chart as 3/5. I need it to be 30% as (Summ of recorded time)/ (Sum of planned time), and use slicer for the filters. 

I will try to play with this a bit and see if I can adapt it. 

Thanks

[Jihwan_Kim · Accepted solution]

Hi, @almaarapi 

I am not quite sure whether I follow your point.

If you want to divide by the sum of planned time, please check the below if it suits your requirement.

https://www.dropbox.com/s/eureyhe950g5rbr/almaarapi.pbix?dl=0 

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.