PowerBI

[ROLFUS]

I have a data set with machine types, say A, B, C, D, and E. Then I have a data set for the service team where some can only service a specific machine type, but others can service multiple types based on their skill set.
Now, I want to display where the machines and technicians are on a map and create a table showing the machine type-to-technician ratio.
I can't get it to work since it has only one type in the machine data, but it could be A, B, or even more in the tech data.
Is there a way to do such an analysis?

[ROLFUS]

Hi Pete,

Serial NumberTECHCityStateZipCountry

Machine001	A	WOODBRIDGE	ON	L4L3T1	CANADA
Machine001	A	MUSKEGON	MI	49444	USA
Machine003	B	BEDFORD	IN	47421	USA
Machine004	B	WEST JEFFERSON	NC	28694	USA
Machine007	C	PULASKI	VA	24301	USA
Machine008	C	PORT HURON	MI	48060	USA
Machine008	D	MAPLE GROVE	MN	55369	USA
Machine008	D	BERKELEY	MO	63134	USA
Machine008	E	ANKENY	IA	50021	USA
Machine008	E	GLASGOW	KY	42141	USA

NAMETECHCITYSTATE/PROVINCEZIP CODE/POSTAL CODECountry

TECH001	A	Gilbert	AZ	85297	USA
TECH018	A	KISSIMMEE	FL	34744	USA
TECH015	A/B	St. Cloud	FL	34769	USA
TECH023	A/B	Waukegan	IL	60087	USA
TECH008	A/C	Santa Ana	CA	92704	USA
TECH009	A/C	Cypress	CA	90630	USA
TECH056	A/C	Russellville	OH	45168	USA
TECH057	A/C	North Royalton	OH	44133	USA
TECH062	A/C	Dundalk	ON	N0C 1B0	CANADA
TECH074	B	Baxter	TN	38544	USA
TECH081	B	Graham	WA	98338	USA
TECH083	B	Wheeling	WA	26003	USA
TECH050	C	Concord	NC	28027	USA
TECH076	C	Houston	TX	77062	USA
TECH011	D	Willington	CT	06279	USA
TECH012	D	Bristol	CT	06010	USA
TECH047	D	Morganton	NC	28655	USA
TECH048	D	Huntersville	NC	28078	USA
TECH005	D/B	Perris	CA	92571	USA
TECH030	D/B	Plymouth	IN	46563	USA
TECH082	D/B	Port Orchard	WA	98367	USA
TECH026	E	Palatine	IL	60074	USA
TECH027	E	Hoffman Estates	IL	60169	USA
TECH037	A/E	Lake Orion	MI	48360	USA
TECH038	A/E	Cambridge	MN	55008	USA
TECH054	E	Springboro	OH	45066	USA

Thats what im looking for. The data are not right because the data dont match

Thank you.

Rolf

[BA_Pete]

Hi Rolf,

Can you give me examples of your expected outputs please?

I need to know what the correct output looks like in order to work out how to produce it from your data.

Pete

[ROLFUS]

Hello Pete,
Below is the output I'm looking for, but the problem is that the technician count per machine is incorrect. The technician total in the left table is 79, but if you count the tech per the machine types, there are only 67 because the ones who can serve more than one type are not counted.
What I'm looking for is that the technicians who can do multiple types are counted on the types they can support (at least to 50%).

Here is a sample what I want to have. I hope that helps

MA Type	Machines	Techn	Ratio MA/TECH
A	133	15.5	8.58
B	257	41.5	6.19
C	43	13.5	3.19
D	12	3.5	3.43
E	22	5	4.40
Grand Total	467	79

Rolf

[BA_Pete · Accepted solution]

Ok, I think I understand what you're going for.

See attached an example PBIX below. What I've basically done is:

-1- Split out your A/B, C/E etc. Techs into new rows in Power Query.

-2- Also in Power Query, created a new dimTech table, that just contains a unique list of all the Tech Codes (A, B, C etc).

-3- Related the new dim table to both Machines and Technicians tables.

-4- Created the following measures:

_noofMachines = DISTINCTCOUNT(machine[Serial Number])

_noofTechnicians = DISTINCTCOUNT(technician[NAME])

_machineToTechRatio = DIVIDE([_noofMachines], [_noofTechnicians], 0)

_techToMachineRatio = DIVIDE([_noofTechnicians], [_noofMachines], 0)

-5- Using dimTech[techCode] in the visual, it gives the following output based on your example data provided:

In terms of getting this working on a map visual, you'll need to create dimension tables for Country/City/Zip etc. and relate to both tables in the same way as dimTech. It might be cleaner if you just create a location code in each of the fact tables, then your dimLocation table can have [locationCode], [Country], [City] etc. to be plotted on the visual.

Pete

[ROLFUS]

Hello Pete,

I did change the distinctcount of the machines to count because i needed the total of machines per tech code.   

Thank you for the help. 

Rolf

[BA_Pete]

Cool, glad it (almost) worked for you 🙂

If you don't need to count unique items in your tables, just the total number of rows, then I'd probably swap DISTINCTCOUNT(table[column]) for COUNTROWS(table), rather than using COUNT.

Depending how large your actual dataset is this may give you some performance improvement.

Pete

[BA_Pete]

Hi @ROLFUS ,

Please provide anonymised example data of both the Machine Types and Technicians tables, as well as examples of your expected outputs please.

Pete