HELP!! Calculating

[RvdHeijden]

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

[v-jiascu-msft]

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,
Dale

[RvdHeijden]

@v-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]

Hi @RvdHeijden,

How does the table 'Schades' connect to others? For example, the damage of [[Avra] Ploeg 1]  is 1. But how can we get the total production?

Best Regards,
Dale

[RvdHeijden]

@v-jiascu-msft thanks for helping

This is the formula that calculates the production

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 ()
)
)

It refers to the Adress tabel with 'related' and here is a picture with the relationships

the lengths come out of the 'Lengtes' table so there is a relationship between them

[RvdHeijden]

@v-jiascu-msft Any ideas ?

[v-jiascu-msft]

Hi @RvdHeijden,

Can you share a dummy file, please? I just can't assemble the information here together.

Best Regards,
Dale

[RvdHeijden]

@v-jiascu-msft i can email you the file if you send me your emailadres

[v-jiascu-msft]

Hi @RvdHeijden,

I have sent my email through Messages. Please check out. Please DON'T send me any sensitive data.

Best Regards,
Dale

[RvdHeijden]

@v-jiascu-msft any ideas ?