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