Forum Discussion
Calculate distinct values
I am joining a table with another table to get the total km of pipes in a city. So the data looks something like this.
| City | Pipe type | Length of pipes | Costs | Year |
| Paris | High rise | 30.00 | 5000 | 1992 |
| Paris | Low rise | 30.00 | 4000 | 1992 |
| Paris | Low rise | 30.00 | 1500 | 1993 |
| London | Low rise | 50.00 | 7000 | 1992 |
| London | High rise | 50.00 | 4390 | 1992 |
| Rome | High rise | 30.00 | 2000 | 1992 |
I have selections for City, Pipe type and Year. I need to calculate Cost per lenght for a selected year. Irrespective of the pipe type, I need to consider average of the lenght and use it to divide with the costs.
But in my scenario, I am diving the costs by lenght of pipes.
Any suggesstions on how to calculate this?
| City | Pipe type | Length of pipes | Costs | Cost per lenght | Year |
| Paris | High rise | 30.00 | 5000 | 166.66 | 1992 |
| Paris | Low rise | 30.00 | 4000 | 133.33 | 1992 |
| Paris | Low rise | 30.00 | 1500 | 50 | 1993 |
| London | Low rise | 50.00 | 7000 | 140 | 1992 |
| London | High rise | 50.00 | 4390 | 87.8 | 1992 |
| Rome | High rise | 30.00 | 2000 | 66.66 | 1992 |
Also, I have a lenght in pipes card which chages on the pipe type, how do I limit it? Any suggesstions whould be highley appreciated.
Anonymous , is it not a new column
divide([Costs],[Length of pipes])
Hi Anonymous ,
According to your description, you could create a measure by the following formula：
Avg = DIVIDE ( MAX ( 'table'[Costs] ), CALCULATE ( AVERAGE ( 'table'[Length of pipes] ), FILTER ( ALL ( 'table' ), 'table'[Year] = MAX ( 'table'[Year] ) ) ) )Then you described in this "Irrespective of the pipe type”, you could use the methods mentioned by PC2790 to cancel interactions, as follows:
Best Regards,
Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
4 Replies
Anonymous , is it not a new column
divide([Costs],[Length of pipes])
Hi Anonymous ,
According to your description, you could create a measure by the following formula：
Avg = DIVIDE ( MAX ( 'table'[Costs] ), CALCULATE ( AVERAGE ( 'table'[Length of pipes] ), FILTER ( ALL ( 'table' ), 'table'[Year] = MAX ( 'table'[Year] ) ) ) )Then you described in this "Irrespective of the pipe type”, you could use the methods mentioned by PC2790 to cancel interactions, as follows:
Best Regards,
Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.