cancel
Showing results for
Did you mean:
Member

## 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 9
Community Support Team

## Re: HELP!! Calculating

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

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Member

## Re: HELP!! Calculating

@v-jiascu-msft its not necesarry to have one formula.

 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

Member

## Re: HELP!! Calculating

@v-jiascu-msft any ideas ?

Community Support Team

## Re: HELP!! Calculating

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

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Member

## Re: HELP!! Calculating

@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

Member

## Re: HELP!! Calculating

@v-jiascu-msft Any ideas ?

Community Support Team

## Re: HELP!! Calculating

Hi @RvdHeijden,

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

Best Regards,
Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Member

## Re: HELP!! Calculating

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

Community Support Team

## Re: HELP!! Calculating

Hi @RvdHeijden,

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

Best Regards,
Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.