Multiplication 3 columns DAX , 2 in the same table , the other column is in another table

[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 thel with *..1 , I want to calculate a multiplication with 3 columns , 2 are price and quantity in transactions table , and the other column rate of changing between currencies  in exchange table , what i want to do is first verifiying the currency column on transactions table if it is different to MAD ,then i should multiply price * Quantity * rate of change :

I tried a lot but it does not work : 

   1 ) this gave me a scaler      

Mesure = [Quantité x Prix] * CALCULATE(SUM(EXCHANGE RATE [Taux de change ]) , FILTER('TRANSACTIONS ' , 'TRANSACTIONS '[Devise] <> "MAD" ))

2) 

Mesure = SUMX('TRANSACTIONS', IF('TRANSACTIONS' ,
'TRANSACTIONS'[Devise ] <> "MAD" ,
'TRANSACTIONS'[Quantité x Prix]
* (LOOKUPVALUE('TRANSACTIONS[col8],EXCHANGE RATE[Identifiant],
'TRANSACTIONS'[Identifiant]))))

3) I did nit understand the results of this one 
Mesure = SUM('Data ANONYM'[Quantité]) * SUM('Data ANONYM'[Prix]) * CALCULATE(SUM(dtRate[Taux de change ]) , FILTER('Data ANONYM' , 'Data ANONYM'[Devise Règ] <> "MAD" ))

THANK YOU IN ADVANCE

[vivran22]

@Shilp1rin-gue 

I believe you have asked the same question on another post. I have posted a solution:

https://community.powerbi.com/t5/DAX-Commands-and-Tips/Multiplying-3-columns/m-p/1130426#M16766

Let me know if this solves the purpose.

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)

Visit my blog:

vivran.in/my-blog


Feel free to email me for any BI needs .


Connect on LinkedIn

Follow on Twitter

[stevedep]

Hi,

This works with:

TransactionValue = SUMX(Transactions;CALCULATE(MIN(Transactions[Price])*MIN(Transactions[Quantity])* IF(ISBLANK(MIN('Currency'[Currency]));1; MIN('Currency'[Conversion]))))

See image below:

Please note the data model:

Power BI file can be found here. 

If this solves your problem please mark as solved. Thanks!

[Shilp1rin-gue]

Thanks for your reply but i did not mean this :

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 first

if Devise Règ  is different to "MAD" then I will this calcul with this : Price * Quantity * Taux de change

else : simply multiply : Price * Quantity 

[stevedep]

I see,

I made a slight modification so it should meet your requirements:

TransactionValue = 
SUMX(Transactions;CALCULATE(MIN(Transactions[Price])*MIN(Transactions[Quantity])* IF(MIN('Transactions'[Devise])="MAD";1; MIN('Currency'[Conversion]))))

 

Updated file can be found here. 

Please accept as solution if this is what you are looking for. Thanks. 

Kind regards,

Steve. 

[stevedep]

Please let us know if this works for you.

[Shilp1rin-gue]

suddenly it doesnt work 

[stevedep]

Can you please provide more information?

[Anonymous]

Not possible, I'm afraid. Where's a date column for the transactions? Please do not cut corners. Show the full tables.

Best
D

[Shilp1rin-gue]

Thanks for your reply sorry it was a fault :

this is the content of Exchange rate table 

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

and this is the transactions table : 

Ident            Date                      Price               Quantity      Devise Règ
  1            28/12/18                  102 7                  10                  EUR
  2            28/12/18                  104 ,08               30                 MAD

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

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

else : simply multiply : Price * Quantity

[Anonymous]

Since the time granularity of both tables is the same, you just use Power Query to perform the calculation of the amount after applying the right exchange rate and create a column with the right amount in the Transactions table. Then it'll be DEAD easy. You will just sum the amounts. You won't even need to import the exchange rate table.

Best
D

[Shilp1rin-gue]

I just took the 2 first rows  , the calcul up is what they imposed on me to do even I tried a lot it does not work 

[Anonymous]

I'm not able to understand what you wrote. Sorry.

Best
D

[Shilp1rin-gue]

it's ok thanks for your reply