How to get a related value of the same group to multiply

[RitaM001]

Hello, I have this issue:

There is a table that has store values (Value2) for each store, but another value (Value1) just for the main store.

They all belong to the same city.

I need the result to be the multiplication of the Value1 (that is only in Store1) for each one of the Value2.

For example, for City1 there are 3 stores, Store1 has the Value1 and I want to multiply for the Value2 of the Store1, Store 2, Store3.

In this case would be

for store1: 50*100, 

for store2: 50*500,

for store 3: 50*200.

How can I indicate in dax that if there is no value for value1 it should look for the value related to the same city?

Thank you!! 

City1	Value1	Value2	Result
Store1	50	100	5000	C2*B2
Store2		500	25000	C3*B2
Store3		200	10000	C4*B2
City2	Value1	Value2	Result
Store1	20	200	4000
Store2		250	5000
Store3		800	16000

[v-kaiyue-msft]

Hi @RitaM001 ,

Did my suggestion help you solve the problem?  If it is, could you please mark the helpful replies as Answered to close this topic?

Best Regards,

Clara Gong

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[RitaM001]

Thank you but the solutions did not work. I dont have the SELECTEDVALUE formula. @aduguid What can use instead of selectedvalue?

I want to explain it better

The ressult I need is the value1 that its calculated in Store1 to multiply the value2 associated to another store that belongs to the same group (city).

City1	Value1	Value2	Result
Store1	50	100	5.000
Store2		500	25.000
Store3		200	10.000

The table that relates the stores and cities is something like this:

City	Stores
City1	Store1
City1	Store2
City1	Store3
City2	Store1
City2	Store2
City2	Store3

Thank you for your time and pattience

[v-kaiyue-msft · Accepted solution]

Hi @RitaM001 ,

Thanks for the reply from @aduguid , please allow me to provide another insight:

Create measure.

Result = 
VAR MainStoreValue1 = 
    LOOKUPVALUE(
        'Table'[Value1], 
        'Table'[City],MAX('Table'[City]), 
        'Table'[Store], "Store1"
    )
RETURN
    MainStoreValue1 * MAX('Table'[Value2])

If your Current Period does not refer to this, please clarify in a follow-up reply.

Best Regards,

Clara Gong

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[aduguid]

try this measure

Result = 
VAR CurrentCity = SELECTEDVALUE(Table[City])
VAR MainStoreValue = CALCULATE(
    MAX(Table[Value1]),
    FILTER(Table, Table[City] = CurrentCity && Table[Store] = "Store1")
)
RETURN
MainStoreValue * MAX(Table[Value2])