cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Sammy_02
Frequent Visitor

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 IV
Super User IV

@Sammy_02 , is it not a new column

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



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

View solution in original post

PC2790
Solution Sage
Solution Sage

Hello @Sammy_02 ,

 

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 @Sammy_02 ,

 

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
Sammy_02
Frequent Visitor

All three answers work as per my requirement. Thanks!

PC2790
Solution Sage
Solution Sage

Hello @Sammy_02 ,

 

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 @Sammy_02 ,

 

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

amitchandak
Super User IV
Super User IV

@Sammy_02 , is it not a new column

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



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

View solution in original post

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors