Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
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 🙂
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
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
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
Hi,
In an earlier post I used such a relationship, is this what you are looking for?
Proud to be a Super User!
Awesome Keyboard Shortcusts in Power BI, thumbs up if you like the article
My Community Blog Articles (check them out!)
My Blog - Power M code to automatically detect column types -
How to create test data using DAX!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
45 | |
26 | |
22 | |
13 | |
8 |
User | Count |
---|---|
73 | |
51 | |
45 | |
16 | |
12 |