Forum Discussion
HELP!! Calculating
hey Guys,
Ik need some help with a formula, ive made a matrix based on a formula that calculates how many meters a certain team has produced based on averages per area which works perfectly.
However the next step is that i need to find out what their damage rate is and with that i mean i know there production in meters lets say 1000 meters and if they have damaged a certain type cable 10 times that means that means they'll have a damage rate of 1 damage every 100 meters
| Production in meters | Number of Damage type A | Number of Damage type B | Number of Damage type C | Number of Damage type D | Number of Damage type E | Number of Damage type F | Number of Damage type G | |
| Team A | 1.156,00 | 4 | 3 | 1 | ||||
| Team B | 23.231,00 | 24 | 11 | 2 | 4 | 1 | 3 | |
| Team C | 28.801,82 | 19 | 7 | 2 | 4 | 3 | 2 | |
| Team D | 20.545,60 | 25 | 9 | 7 | 2 | 3 | ||
| Total | 73.734,43 | 50 | 17 | 12 | 8 | 5 |
If i have to value 'Production in Meters' as a calculated column in this table i can calculate the averages.
However that value is being calculated base on this formula
Gegraven m1 (productie) =
IF (
Adressen[civiel gesloten] <> BLANK ();
SWITCH (
TRIM ( Adressen[Gebiedstype] );
"Kern"; RELATED ( Lengtes[Gemiddelde graaflengte Kern] );
"Buitengebied"; RELATED ( Lengtes[Gemiddelde graaflengte Buitengebied] );
"Industrieel"; RELATED ( Lengtes[Gemiddelde graaflengte Industrieel] );
BLANK ()
)
)
What i need to calculate, based everytime on the production of that TEAM per type cable, is:
Damagequote=DIVIDE [Production in meters] / [Total amount of damages on Cable type A]
Damagequote=DIVIDE [Production in meters] / [Total amount of damages on Cable type B]
Damagequote=DIVIDE [Production in meters] / [Total amount of damages on Cable type C]
Damagequote=DIVIDE [Production in meters] / [Total amount of damages on Cable type D]
etc..
etc..
Im hoping we can calculate that base on one formula but i need multiple colums and not a measurement
9 Replies
Hi RvdHeijden,
You need multiple columns. So is it necessary to use only one formula?
Seems you already have the formulas. Can you share the original data and the expected result?
Best Regards,
Dalev-jiascu-msft its not necesarry to have one formula.
Table 'Adressen'
Straatnaam Huisnummer Toevoeging Civiel gesloten DP naam Gebiedstype Zitterstraat 2 24-9-2018 STAS_1E_DP035 Kern Zitterstraat 4 18-5-2018 STAS_1E_DP037 Kern Zitterstraat 6 A 14-9-2018 STAS_1E_DP035 Buitengebied Zitterstraat 8 2-8-2018 STAS_1E_DP035 Industrieel Zitterstraat 10 4-9-2018 STAS_1E_DP036 Industrieel Zitterstraat 12 STAS_1E_DP037 Buitengebied Zitterstraat 14 STAS_1E_DP037 Kern Zitterstraat 16 8-8-2018 STAS_1E_DP040 Kern Tabel 'Lengths'
DP naam Average length Kern Average length Buitengebied Average length Industrieel STAS_1E_DP035 56,0 124,6 89,5 STAS_1E_DP036 30,0 63,4 48,6 STAS_1E_DP037 29,5 89,6 74,9 STAS_1E_DP040 14,9 111,4 65,0 These were the tables on which the formula was based, this way i got the total amount of meters per team based on a certain area and buildingtype.
I also have a different Table named 'Schades' were we register the damages that occured during our work.
That table looks like this
Streetname Housenumber Addition Damaged Infrastructure Team Exportstraat 7 CAI [Avra] Ploeg 1 Gemertseweg 122 A CAI [Konax] Ploeg 3 Nullen 11 Koper [Konax] Ploeg 3 Peelkant 70 Gas [Konax] Ploeg 5 Peelkant 112 B Water [v. Beers] Ploeg 3 So that means for example that [Konax] Ploeg 3 has caused 2 damages (1 time the CAI cable and 1 time Koper kabel)
In the first formula we calculated that [Konax] Ploeg 3 has a production of 2500 m1 and based on the 2 damages they have a damage quote of 2500 / 2 = 1250, so that means they have 1 damage for every 1250 meters.
I want that calculation per typ of damage (infrastructure) and per Team but given the relationships between the tables that shouldn't be a probleem
The expected outcome should be something like
Damaged Infrastructure [Avra] Ploeg 1 [Konax] Ploeg 3 [Konax] Ploeg 5 [v. Beers] Ploeg 3 CAI 800 900 1500 1200 Koper 2000 1600 500 1200 Gas 1500 500 1200 1500 Water 3000 3000 2500 4000 Totaal 2200 1200 1800 2700 The values should be the SUM of total amount in Meters devided by the number of damages of a certain Infrastructure and off course an overall average
v-jiascu-msft any ideas ?
