Calculating with different variables HELP !!!

[RvdHeijden]

Goodday,

I have a table which has the average lengths per city and i want to calculate the amount of meters that has been digged over multiple cities per contractor.

I have a table which has the average meters per city.

City                Centre            Rural

A                      52                  110   

B                      25                  260  

C                      15                  115  

and in another table i have the number of houses that have been build

City                Centre            Rural

A                      10                  2   

B                      25                  5  

C                      6                  12 

What i need is a formula that calculates the total amount of meters that have been digged.

So basically the fomula needs to check how many houses were build and then checks (in the other table) what the average meters are for that city and then sums them up in a total meters digged 

[v-shex-msft]

Hi @RvdHeijden,

I'd like some sample data for testing and coding formula.

Regards,

Xiaoxin Sheng

[RvdHeijden]

Hey @v-shex-msft thank you for responding.

Ive been working on my report and im a bit further but not quite there yet.

In the first picture you can see the column 'Onderaannemer' which is basically the contractor that will build the network for us with below that the teams of that contractor. In the other columns (the last 3) you can see the nummer of 'Adresses' that have been build in the 'kern', 'Buitengebied' and 'Industrieel. These are locationtypes of houses.

But here comes the difficulty, below the teams for example [Persorent] Ploeg 2 you see a number of areas like WNRO_1F_DP009 and so on and you can see what types and how many houses have been build per area.

In the table below (different table)  i have all the areas (unique) and the average length per area per location type.

I need a formula that can calculate the total meters that have been digged by counting the amount of houses per location type and per are and then multiplying that with the average length of that area and location type

For example

STAS_1E_DP005, i have build 5 houses in 'Kern' and 10 houses in 'Buitengebied' that means (5 * 17,2) + (10 * 177) = 86 + 1770 = 1856 meters.

But further in the project i will have build hundreds of houses in a lot of areas with different averages and the formula still needs to be able to calculate that.

[v-shex-msft]

Hi @RvdHeijden,

I'd like to suggest you unpivot your columns to convert table as category and attribute /value part.(attribute: location types , value: amount)

Then you can create a matrix visual with Ondernemer column as rows, attribute as columns field.

After these steps, write a measure to check current row and column to find out correspond records to calculate with current amount.

Power Query Unpivot Scenarios

For get current row contents, you can try to use selectedvalue function.

SELECTEDVALUE Function

Regards,

Xiaoxin Sheng

[RvdHeijden]

@v-shex-msft im not sure which column i need to unpivot because im not a pro at powerbi.

Can you tell me which colum i need to unpivot and what the result is that you are expecting ?

im not sure which colums i can unpivot more then they already are