The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now
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.
Solved! Go to Solution.
@Anonymous , is it not a new column
divide([Costs],[Length of pipes])
Hello @Anonymous ,
As I understand from your requirement, you want to limit the cost per length to be limited with selected of city and pipe type and only want the value to be updated with year.
You can make use of edit interactions in this particular case.
Refer the sample file here
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.
All three answers work as per my requirement. Thanks!
Hello @Anonymous ,
As I understand from your requirement, you want to limit the cost per length to be limited with selected of city and pipe type and only want the value to be updated with year.
You can make use of edit interactions in this particular case.
Refer the sample file here
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.
@Anonymous , is it not a new column
divide([Costs],[Length of pipes])
User | Count |
---|---|
160 | |
110 | |
96 | |
86 | |
75 |
User | Count |
---|---|
157 | |
137 | |
132 | |
81 | |
61 |