Multiply values from one table and another

[Orstenpowers]

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...

I hope someone out there can help me...

[v-yajiewan-msft]

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

Is there any progress on this issue?

If you find any answer is helpful to you, please remember to accept it.

It will help others who meet the similar question in this forum.

Thank you for your understanding.

[parry2k]

@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]

@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?

[v-yajiewan-msft]

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.

[DataNinja777]

Hi @Orstenpowers ,

My recommendation is to create explicit measures rather than relying on implicit ones, as this will allow you to multiply those measures more effectively. The three columns from the two different fact tables seem to be filtered by a common dimension table, which is applied in the row context of your table visual. By using explicit measures, the multiplication will be correctly filtered by the same row context.

Best regards,

[parry2k]

@Orstenpowers do you want add a measure or a column? Also, all these multipliers are columns or measures?

[Orstenpowers]

Hi and Thanks for your feedback!

All multipliers are columns.

You are asking whether I prefer measure or column as solution?! Actually I do not know the Pro's and Con's of both...it just needs to work. 🙂