topic Multiply two values from the same table in DAX Commands and Tips

Multiply two values from the same table

mcmum01 — Thu, 22 Jul 2021 17:02:35 GMT

Hi all

I have a table with transactions in it as well as well as rate per transaction. I would like to write a dax formula that finds the rate for the month and for the customer, and multiple it together to get to revenue. I could separate the transactions and rates into 2 different tables, but wanted to try something different. Example: ACME01, 1/1/21 = 100 * 10 = 1000. ACME01, 2/1/21 = 110 * 20 = 2200, etc. Is there any easy way to do this or should I just separate the rates and transactions into 2 tables?

GP Vendor ID	Measure	Value	Month
ACME01	transactions	100	1/1/2021
ACME01	rate	10	1/1/2021
ACME01	transactions	110	2/1/2021
ACME01	rate	20	2/1/2021
ABCE01	transactions	115	3/1/2021
ABCE01	rate	30	3/1/2021

Re: Multiply two values from the same table

Jihwan_Kim — Thu, 22 Jul 2021 17:22:27 GMT

Result measure : =

CALCULATE ( SUM ( Data[Value] ), MeasureTable[Measure] = "transactions" )

* CALCULATE ( SUM ( Data[Value] ), MeasureTable[Measure] = "rate" )

https://www.dropbox.com/s/ftd4u2cr2pr0qk5/mcmum.pbix?dl=0

Re: Multiply two values from the same table

mcmum01 — Thu, 22 Jul 2021 18:09:15 GMT

I tried that and I got some really big numbers. I gave you a small sample of a larger table. Would it be different if there were multiple GP Vendor IDs in a month? Here is an example:

GP Vendor ID	Store	Measure	Value	Month
ACME01	1	transactions	100	1/1/2021
ACME01	1	rate	10	1/1/2021
ACME01	2	transactions	110	1/1/2021
ACME01	2	rate	20	1/1/2021
ACME01	3	transactions	115	1/1/2021
ACME01	3	rate	30	1/1/2021