Use a measure value of one row to populate all other rows

[MonishaM]

Hi,

Thanks for taking the time to go through this.

I have been breaking my head over this for the past few days and can't seem to find a valid solution. 

I have two tables - One with category headings (Category) and the other with component values (Component). I have one measure called 'Valid' that shows a value of 1 for the first nonblank value in the component table for the category. The result looks like this.

I have another measure called 'Market value' that returns a value based on some calculation.

Now my problem is with the measure 'Beta'. Beta for any component is calculated as the market value of the valid component of the category of the current component. Like in the above screenshot the valid component of the fruits category is Apples. So Beta is supposed to be 0.91. So the expected result is supposed to look like this:

But the output I get is as below:

The current formula for beta is as follows:

Beta:=
var fnb =FIRSTNONBLANK('Component'[Component],[valid])
RETURN CALCULATE( [Market Value],'Component'[Component]=fnb)

Can someone please help me with this? I have tried adding all filter, sumx but nothing seems to work.

Thanks & regards,
Monisha

[MonishaM · Accepted solution]

Finally figured out the answer to this and all it needed was an 'ALL()' 

Beta:=
var fnb =FIRSTNONBLANK(All('Component'[Component]),[valid])
RETURN CALCULATE( [Market Value],'Component'[Component]=fnb)

Hope this helps whoever is struggling with the this kind of issue.

[AntonioM]

Hi, please give this a try

Beta = CALCULATE([Market Value], REMOVEFILTERS('Component'[Component]), Filter('Component', [valid] = 1))

For each component, that should find all components in its category, find which one is valid and then return its market value. 

If that still doesn't work please let me know. 

[MonishaM]

@AntonioM : Unfortunately that doesn't work. It is still only populating Beta value for Apples but this time with an incorrect value.

[AntonioM]

@MonishaM 

Ok, I've given this a try and it seems to work for me (although I've guessed a little bit how your model is set up). 

Beta = CALCULATE( CALCULATE([Market Value], FILTER(Component, [Valid] = 1)), ALL(Component[Component]))

If this also doesn't work please can you share some of your data model (tables, measures, relationships)

[MonishaM]

@truptis : Without that condition how will I get the market value of Apples as the beta value of all the components under fruits?

[truptis]

Hi @MonishaM , This is happening because of this condition : 'Component'[Component]=fnb