How to combine 2 measures from different tables

[PBI_newuser]

Hi,

I wanna lookup the measure from Table A to Table B.

Table A:

I have calculate a measure Available Technician = DISTINCTCOUNT(Technician).

Line	Group	Technician
ABC	ABC-123	Sam
ABC	ABC-123	Lilly
ABC	ABC-234	Billy
BCD	BCD-123	Cath
BCD	BCD-234	Edward

Table B:

In this table, I calculate Current Technician = DISTINCTCOUNT(Technician)

Line	Group	Technician	Total hours
ABC	ABC-123	Aaron	12.5
ABC	ABC-234	Ben	4.5
BCD	BCD-234	Edward	16.3
ABC	BCD-234	James	3.2

I would like to combine these 2 information in one table as below:

Line	ABC		BCD
Group	ABC-123	ABC-234	BCD-123	BCD-234
Available Technician	2	1	1	1
Current Technician	1	1	0	2

[parry2k]

@PBI_newuser see attached, you can tweak it as you see fit.

Check my latest blog post Compare Budgeted Scenarios vs. Actuals I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[PBI_newuser]

Hi @parry2k ,

Actually I wish to calculate the Available technician = contribution of each technician for each product.

Please see the explanation below. Sample is here.

[v-henryk-mstf]

Hi @PBI_newuser ,

Since i cannot open the link of your newly shared file, I tested it on the original data model: created a calculated column to count the number of Line and Group respectively, and finally use the divide function to calculate the proportion of each technician:

C = 
VAR a =
    CALCULATE (
        COUNTROWS ( 'Table' ),
        FILTER ( ALL ( 'Table' ), 'Table'[Line] = EARLIER ( 'Table'[Line] ) )
    )
VAR b =
    CALCULATE (
        COUNTROWS ( 'Table' ),
        FILTER ( ALL ( 'Table' ), 'Table'[Group] = EARLIER ( 'Table'[Group] ) )
    )
RETURN
    DIVIDE ( b, a )

In the matrix table, replace the previous field with the calculated column created and put it in the value to get the following result:

If it still does not solve your problem, please confirm whether the shared file link can be opened correctly, or change the sharing method. So that I can use the correct data for further testing. Looking forward to your reply.

Here is the sample pbix file.

Best Regards,
Henry

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

[PBI_newuser]

Hi @v-henryk-mstf , is the link working? 

[PBI_newuser]

Hi @v-henryk-mstf , it doesn't work. 

Please find the shared file URL here. 

[parry2k]

@PBI_newuser where you get from available vs current technician? It is not in the tables? what is the logic for it?

[PBI_newuser]

Hi @parry2k  I created a measure "Current Technician"  in Table B and "Available Technician" in Table A.

Current Technician = DISTINCTCOUNT(Technician)

Available Technician = DISTINCTCOUNT(Technician)

I wanted to compare the count of technician with its associated Line & Group.