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.
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])
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 |
---|---|
114 | |
97 | |
86 | |
70 | |
62 |
User | Count |
---|---|
151 | |
120 | |
103 | |
87 | |
68 |