Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Reply
Anonymous
Not applicable

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.

 

CityPipe typeLength of pipesCostsYear
ParisHigh rise30.0050001992
ParisLow rise30.0040001992
Paris Low rise30.0015001993
London Low rise50.0070001992
LondonHigh rise50.0043901992
RomeHigh rise30.0020001992

 

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? 

CityPipe typeLength of pipesCostsCost per lenghtYear
ParisHigh rise30.005000166.661992
ParisLow rise30.004000133.331992
Paris Low rise30.001500501993
London Low rise50.0070001401992
LondonHigh rise50.00439087.81992
RomeHigh rise30.00200066.661992

 

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.

@amitchandak 

3 ACCEPTED SOLUTIONS
amitchandak
Super User
Super User

@Anonymous , is it not a new column

divide([Costs],[Length of pipes])

View solution in original post

PC2790
Community Champion
Community Champion

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 

 

View solution in original post

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:

1.png


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.

View solution in original post

4 REPLIES 4
Anonymous
Not applicable

All three answers work as per my requirement. Thanks!

PC2790
Community Champion
Community Champion

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:

1.png


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.

amitchandak
Super User
Super User

@Anonymous , is it not a new column

divide([Costs],[Length of pipes])

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.