I am trying to find a way to choose specific ID, have its values read and find all Products with the same name in different IDs and multiply the first ID X.values into a variable of the other products in different IDs.

so for example I have data below :

Raw Data

and I want to Choose ID = 1234 to find all the Volumes associated to the products of 1234. Find the products with the same name in other IDs and Multiply each Product volumes of ID=1234, into the same name product X.Value of the other ID, like Below : required Calculation

Thanks

1 Answers

1
Olly On Best Solutions

In a calculated column, you can use LOOKUPVALUE to find the Volume for ID 1234 and the corresponding Product - then choose how to calculate your output based on whether a matching value was returned or not:

New Column = 
VAR LookupID = 1234
VAR LookupVolume = 
    LOOKUPVALUE ( 
        Table1[Volume],
        Table1[ID], LookupID,
        Table1[Product], Table1[Product]
    )
RETURN
    Table1[X.Value] & " * " & 
    IF ( 
        ISBLANK ( LookupVolume ),
        Table1[Volume],
        LookupVolume
    ) 

Worked example PBIX file: https://pwrbi.com/so_55916210/

EDIT

More complex as a measure - not entirely clear how you intend using it, but this approach creates a separate table for a list of ID values, to be used as a slicer:

ID List = DISTINCT ( Table1[ID] )

Then we can use measure:

New Measure = 
SUMX ( 
    Table1,
    VAR LookupID = 
        IF ( 
            HASONEVALUE ( 'ID List'[ID] ),
            VALUES ( 'ID List'[ID] ),
            BLANK()
        )
    VAR LookupProduct = 
        IF ( 
            HASONEVALUE ( Table1[Product] ),
            VALUES ( Table1[Product] ),
            BLANK()
        )
    VAR EffectiveVolume =
        CALCULATE ( 
            SUM ( Table1[Volume] ),
            ALL ( Table1 ),
            Table1[ID] = LookupID,
            Table1[Product] = LookupProduct
        )
    RETURN
    Table1[X.Value] * 
    IF ( 
        ISBLANK ( EffectiveVolume ),
        Table1[Volume],
        EffectiveVolume
    )
) 

Updated PBIX file: https://pwrbi.com/so_55916210-2/