Forum Discussion
PowerBI
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?
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
7 Replies
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
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
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
