1

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

Negar
  • 45
  • 1
  • 4

1 Answers1

1

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/

Olly
  • 7,749
  • 1
  • 19
  • 38
  • Thank you very much, this helped a lot, however I am looking for a way not to have to create a new column and having the measure there in a way that when I choose the particular ID from the drop down list, it will show me the result. right now I see I need to create a new column for each ID, which if I have 1000s of IDs .... it might be not possible. is there a way like that? – Negar Apr 30 '19 at 10:36
  • can we develop this further by saying we chose 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? – Negar Jun 16 '19 at 11:52