cancel
Showing results for
Did you mean:
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.

 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.

3 ACCEPTED SOLUTIONS
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!

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

Community Support

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:

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

All three answers work as per my requirement. Thanks!

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

Community Support

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:

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.

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!

Announcements

#### Welcome to the User Group Public Preview

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

#### Microsoft Business Applications Summit sessions

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

#### Power BI Dev Camp - June 24th

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors