Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
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
@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
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
@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
Hi @RvdHeijden,
Can you share a dummy file, please? I just can't assemble the information here together.
Best Regards,
Dale
Hi @RvdHeijden,
I have sent my email through Messages. Please check out. Please DON'T send me any sensitive data.
Best Regards,
Dale
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
99 | |
75 | |
73 | |
49 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |