powerbiformulavisualize

Automated formula to select values from a specifid ID and multiply to the rest with the same Name


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


Solution

  • 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/