Forum Discussion

Orstenpowers's avatar
Orstenpowers
Post Patron
1 year ago

Multiply values from one table and another

Dear all, I need to multiply the values from the three columns "AMOUNT_GROUP", "Faktor für x-Leiter" and "Multiplier". I assume this is quite easy to solve, but obviously I am too stupid...   ...
Need Help
parry2k's avatar
parry2k
Super User
1 year ago

Orstenpowers if you have all these columns in a the same table, just add new column using following DAX expression:

 

New Column = Table[Col 1] * Table[Col 2] * Table[Col 3]

 

Although it is recommended to add calculated columns in the PQ. 

  • Orstenpowers's avatar
    Orstenpowers
    Post Patron
    1 year ago

    parry2k !

    Thank you very much for your proposal, but unfortunately the data are not in the same table.

     

    Sales Statistic Group (full) is coming from ST_PRODUCT_GROUP

    AMOUNT_GROUP is coming from BILLING_DATA

    Faktor für x-Leiter and Multiplier are coming from ETSI_Multipliers

     

     

    I also had hoped that I would only need to multiply these three values, but it did not work out...

    Do you have any idea what I need to do?

    • Anonymous's avatar
      Anonymous
      Not applicable
      1 year ago

      Hi Orstenpowers , hello parry2k and DataNinja777, thank you for your prompt reply!

       

      Is there a relationship between these three tables?

       

      If so, use similar syntax for your requirements:

      CalculatedAmount = 
SUMX (
    ST_PRODUCT_GROUP,
    BILLING_DATA[AMOUNT_GROUP] *
    RELATED(ETSI_Multipliers[Faktor für x-Leiter]) *
    RELATED(ETSI_Multipliers[Multiplier])
)

       

      If false, we need to manually search for the relevant value using LOOKUPVALUE(Note,here is the sample code):

      CalculatedAmount = 
BILLING_DATA[AMOUNT_GROUP] *
LOOKUPVALUE(
    ETSI_Multipliers[Faktor für x-Leiter],
    ETSI_Multipliers[Sales Statistic Group (full)], ST_PRODUCT_GROUP[Sales Statistic Group (full)]
) *
LOOKUPVALUE(
    ETSI_Multipliers[Multiplier],
    ETSI_Multipliers[Sales Statistic Group (full)], ST_PRODUCT_GROUP[Sales Statistic Group (full)]
)

      More information for your reference:

      RELATED function (DAX) - DAX | Microsoft Learn

      LOOKUPVALUE function (DAX) - DAX | Microsoft Learn 

       

      Best regards,

      Joyce

      If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.