cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
RvdHeijden Member
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 metersNumber of Damage type ANumber of Damage type BNumber of Damage type CNumber of Damage type DNumber of Damage type ENumber of Damage type FNumber of Damage type G
Team A1.156,004 31   
Team B23.231,0024112413 
Team C28.801,821972432 
Team D20.545,60259723  
Total73.734,4350171285  

 

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
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.
RvdHeijden Member
Member

Re: HELP!! Calculating

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

 

Table 'Adressen'

StraatnaamHuisnummerToevoegingCiviel geslotenDP naamGebiedstype
Zitterstraat 2 24-9-2018STAS_1E_DP035Kern
Zitterstraat 4 18-5-2018STAS_1E_DP037Kern
Zitterstraat 6A14-9-2018STAS_1E_DP035Buitengebied
Zitterstraat 8 2-8-2018STAS_1E_DP035Industrieel
Zitterstraat 10 4-9-2018STAS_1E_DP036Industrieel
Zitterstraat 12  STAS_1E_DP037Buitengebied
Zitterstraat 14  STAS_1E_DP037Kern
Zitterstraat 16 8-8-2018STAS_1E_DP040Kern

 

Tabel 'Lengths'

DP naamAverage length KernAverage length BuitengebiedAverage length Industrieel
STAS_1E_DP03556,0124,689,5
STAS_1E_DP03630,063,448,6
STAS_1E_DP03729,589,674,9
STAS_1E_DP04014,9111,465,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

 

StreetnameHousenumberAdditionDamaged InfrastructureTeam
Exportstraat7 CAI[Avra] Ploeg 1
Gemertseweg122ACAI[Konax] Ploeg 3
Nullen11 Koper[Konax] Ploeg 3
Peelkant70 Gas[Konax] Ploeg 5
Peelkant112BWater[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
CAI80090015001200
Koper200016005001200
Gas150050012001500
Water3000300025004000
Totaal2200120018002700

 

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

RvdHeijden Member
Member

Re: HELP!! Calculating

@v-jiascu-msft any ideas ?

Community Support Team
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.
RvdHeijden Member
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

 

2018-10-08_0943.png

RvdHeijden Member
Member

Re: HELP!! Calculating

@v-jiascu-msft Any ideas ?

Community Support Team
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.
RvdHeijden Member
Member

Re: HELP!! Calculating

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

Community Support Team
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.