Multiplying 3 columns

[Shilp1rin-gue]

Hello everybody i hope that you are doing well , I'm a beginner on Dax I NEED SOME HELP PLEASE ,I have a table TRANSACTIONS and the other table for EXCHANGE RATE , i related them with *..1 , I want to calculate a multiplication with 3 columns

this is the content of Exchange rate table 

Ident          Date     currency          exchange rate 
1                28/12        USD                    9,53
2                28/12        EUR                    10,93 

and this is the transactions table : 

Ident            Price                  Quantity      Devise Règ
  1                  102,7                  10                  EUR
  2                 104 ,08                30                 MAD

what I want to do is multiply :  Price * Quantity * Taux de change with a condition that verify 

if Devise Règ is different to "MAD" then I will do the calcul with this : Price * Quantity * exchange rate

            = 102,7 * 10 * 10,93

else : simply multiply : Price * Quantity 

             = 104,08 * 30

I tried this but it doesn't twork : 

Measure = SUM(' TRANSACTIONS'[Quantity]) * SUM(' TRANSACTIONS'[Price]) * CALCULATE(SUM(Exchange rate [exchange rate  ]) , FILTER(' TRANSACTIONS' , ' TRANSACTIONS'[Devise Règ] <> "MAD" ))

Thank you in advance

[Anonymous]

Hi @Shilp1rin-gue,

I tried doing it using Calculated Column. Below is the data that I took.

Exchange Rate Table:

Transaction Table:

Assuming a relationship exists between the two tables, I created a calculated column in Transaction table as below:

This calculated column i.e. "Exchange_Rate" has value of "Exchange Rate" column from Exchange Rate table if Devise Reg is not MAD else it is 1.

Now, I created a measure using Price, Quantity and calculated column "Exchange_Rate" as below:

I hope this is what you were looking for.

Please mark this post as solution if it solved your purpose.

Thanks 🙂

[vivran22]

Hello @Shilp1rin-gue ,

I am not sure on which column you have created the relationship, hence the following solution is without relationship

You may try this:

For Calculated Column

Calc = 
IF (
    dtQty[DeviseRèg] <> "MAD",
    dtQty[Price] * dtQty[Quantity]
        * LOOKUPVALUE ( fxRate[exchangerate], fxRate[currency], dtQty[DeviseRèg] ),
    dtQty[Price] * dtQty[Quantity]
)

For Measure:

Calc M = 
VAR _Calc_MAD =
    SUMX (
        FILTER ( dtQty, dtQty[DeviseRèg] = "MAD" ),
        dtQty[Price] * dtQty[Quantity]
    )
VAR _Calc =
    SUMX (
        FILTER ( dtQty, dtQty[DeviseRèg] <> "MAD" ),
        dtQty[Price] * dtQty[Quantity]
            * LOOKUPVALUE ( fxRate[exchangerate], fxRate[currency], dtQty[DeviseRèg] )
    )
VAR _Return =
    IF (
        HASONEVALUE ( dtQty[Ident] ),
        IF ( SELECTEDVALUE ( dtQty[DeviseRèg] ) = "MAD", _Calc_MAD, _Calc )
    )
RETURN
    _Return

Cheers!

Vivek


If it helps, please mark it as a solution
Kudos would be a cherry on the top 🙂 (Hit the thumbs up button!)


If it doesn't, then please share a sample data along with the expected results (preferably an excel file and not an image)

https://www.vivran.in/
Connect on LinkedIn
Follow on Twitter

[Shilp1rin-gue]

thank you for your reply, i related them with ident 1..* , 1 from EXCHANGE TABLE to * TRANSACTIONS  ,

for the column expression, you show me it gave 

"A table of multiple values was supplied where a single value was expected" :don't know why I think because I have many rows or the expression must be changed actually I'm a beginner I'm still confused 

 and the measure it gave nothing 

[vivran22]

@Shilp1rin-gue 

Can you share more sample data to get a better understanding?

Cheers!
Vivek

Visit my blog:
vivran.in/my-blog

Feel free to email me for any BI needs .
Connect on LinkedIn
Follow on Twitter

[stevedep]

Hi,

In an earlier post I used such a relationship, is this what you are looking for?

https://community.powerbi.com/t5/DAX-Commands-and-Tips/Multiplication-3-columns-DAX-2-in-the-same-ta...