New measure to take the value from 1 row and apply it to all rows in the table

[tommuirwebb]

I have a simple table with columns Fruit and Cost. I would like to add a new measure which takes the value of Cost where Fruit="Pear" and apply it to all rows like so:

Fruit	Cost	New measure
Apple	1.0	1.2
Pear	1.2	1.2
Banana	0.9	1.2
Orange	0.8	1.2

[v-cherch-msft · Accepted solution]

Hi @tommuirwebb 

You may try below measure:

Measure =
CALCULATE ( [Cost], FILTER ( ALL ( Table1 ), Table1[Fruit] = "Pear" ) )

Regards,

[tommuirwebb]

This answer worked for me. Thanks so much for your help.

[Anonymous]

@tommuirwebb  - Create this Measure:

Pear Value = CALCULATE(MAX(FruitTable[Cost]), FruitTable[Fruit] = "Pear")

[tommuirwebb]

Hi natelpeterson, thanks for your reply. I had tried this already but this only applies the value for the row which contains Pear. I'd like the Cost value for Pear to apply to all rows in the table.

[Anonymous]

@tommuirwebb -

It's working here:

What is happening there?

Cheers,

Nathan

[tommuirwebb]

Ok, so I should start by saying I'm new to PowerBI so forgive me if I've missed something fundamental here. In my example, the Cost column is actually a Measure which is a calculated average price for each fruit. I'm guessing this is why I'm seeing different results?

[Anonymous]

@tommuirwebb  - Try this:

Pear Cost = CALCULATE([Cost], FruitTable[Fruit] = "Pear")

[tommuirwebb]

Thanks again, but unfortunately I get the same result. It only applies the value for the row which contains Pear.

[Anonymous]

@tommuirwebb  - could you share your pbix or a screenshot of the model diagram + the code of the Cost measure?