topic Re: How to get a related value of the same group to multiply in DAX Commands and Tips

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

RitaM001 — Fri, 05 Jul 2024 20:30:04 GMT

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

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

aduguid — Sat, 06 Jul 2024 02:21:41 GMT

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])

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

Anonymous — Mon, 08 Jul 2024 02:03:56 GMT

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.

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

RitaM001 — Mon, 08 Jul 2024 16:57:52 GMT

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

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

Anonymous — Tue, 16 Jul 2024 08:35:08 GMT

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.