I am trying to further develop a formula which has condition as below : Choose the desired ID, it will look at the Volume value of the Products of that ID and it will multiply that volume value into the x.Value of the same products with different IDs.
Now I want to choose the products, and if an specific ID does not have that particular product I want it to still return the "chosen ID's Product's "X Value" * chosen ID's Product's Volume value and show in my Stack column.
so for example from attached Data set I would like to choose an ID for example 4321, and it contained product that wasn't available in the other ID's , it will choose the X and volume value of 4321 and show us in the stacked columns? So For example, the product E does not exists in the ID 1234 and 5566 so when it is showing us the final numbers with the volume of 4321 in a, B , C it will also return the Product E into them?
here is the previous formula which needs to be further developed and the previous post which is related to this question :
Desired Volume Measure
SUMX (
Table2,
VAR LookupID =
IF (
HASONEVALUE ( 'ID List'[ID ] ),
VALUES ( 'ID List'[ID ] ),
BLANK()
)
VAR LookupProduct =
IF (
HASONEVALUE ( Table2[Product] ),
VALUES ( Table2[Product] ),
BLANK()
)
VAR EffectiveVolume =
CALCULATE (
SUM ( Table2[Volume] ),
ALL ( Table2 ),
Table2[ID ] = LookupID,
Table2[Product] = LookupProduct)
RETURN
(Table2[X.Value]) *
IF (
ISBLANK ( EffectiveVolume ),
Table2[Volume],
EffectiveVolume
)
)
Automated formula to select values from a specifid ID and multiply to the rest with the same Name
I tried adding a condition which I thought might be useful, but unfortunately it doesnt work by adding :
VAR EffectiveXValue =
CALCULATE (
SUM ( Table2[X.Value] ),
ALL ( Table2 ),
Table2[ID ] = LookupID,
Table2[Product] = LookupProduct)
RETURN
IF(ISBLANK(ISFILTERED(Table1[Product])),EffectiveXValue,Table2[X.Value])*
IF (
ISBLANK ( EffectiveVolume ),
Table2[Volume],
EffectiveVolume
)
)
Thanks a Million