Currency conversion with currency and year filters
I have 4 tables downloaded from the bank regarding exchange rates (CurrencyRates_2020,CurrencyRates_2021, etc.) in which there are columns with currencies, months and exchange rates, in the finance table there are columns amounts, inflows, outflows, date. I also added a table that contains currency column that I used to create the filter. What I would like to do is create a measure for amounts, outflows and inflows that will convert to the currency selected by the filter in the year selected by the filter. I've only written the part for the selected currency , but I don't know how to add a date filter. I would like it to fetch me data from the file "CurrencyRates_2021" when I select the date 2021 etc. I wanted to add at the beginning IF(AND(ISFILTERED('Currency'[Currency]),ISFILTERED('Consulting Finance'[Year]) [...] But I have no idea what to write next.
DAX currency translation
Hi, I would be really greatful is someone could assist. I'm very new to power BI and after searching a lot I'm still struggling for a solution here that suits my setup. My data is all in one currency & I want to convert it into different versions of dollar rates. The DAX query i pulled together worked okish when I had the same rate for each period (I dont have dates in the data, just numbered financial periods 1 to 12). However some of my currency scenarios have a different rate per period and they pull in blank, so I need help changing this query to take into account the rates at different periods. The second issue is in my value column I'm converting there is also volume that I do no want to convert and keep at the same value, this pulls from my P&L column in the data sheet Current DAX formula: FX adjusted value = var value_ = CALCULATE(DATASHEETS[Sum of Value]) var ExchangeRate_ = selectedvalue('Currency'[Currency rate]) return value_ * ExchangeRate_ Data sheet: Currency sheet: Also including relationships if this helps - the Period reference is what links them Thanks for any assistancesummation of currency not working
Hi! I have a table with AgreementLines all in local currency. Example: I have currencyRate table to be able to make a measure to calculate the currency of each row to NOK. I made a measure on my AgreementLine Table called "Total NOK": Total NOK = VAR _currency = MAX(AgreementLine[Cur] ) VAR _conversionRate = CALCULATE( MAX( CurrencyRate[Rate] ) ,CurrencyRate[TransactionCurrency] = _currency ) VAR _TotalInNok = SUM(AgreementLine[Total]) * _conversionRate RETURN _TotalInNok This works very well and the numbers are correct per row in my AgreementLine table. The problem is if I want to sum the field "Total NOK" in a table view or Card. TableView will end up like this: The correct sum for "Total NOK" would be: 39 432,53. I understand the logic, but what I want to achieve is to SUM each row of "Total NOK" which is already calculated. What is the best approach to deal with local currency and get this setup for my reports?Handling very small numbers
Hi everyone! I'm having a problem with the very small numbers in my data; and the measures I created with them look like this; I'm trying to get rid of this scientific notation, and I tried some suggestions I've found here in Community, but they didn't solve my problem. The first suggestion was converting the measure to Currency format, but in that case, there are still a lot of zeros, is there any way that you can share with me to decrease the number of zeros? And I don't understand why it's between parenthesis? Couldn't remove them either. And on the other hand, there shouldn't be a dollar sign here or any sign actually I just need to see the value only, but if it's not possible to use it without the currency sign, it should be € and when I change it, it returns only 0. So I really don't know what to do with these tiny values, if you can help me with any solution, Dax formula, or any way to round it for example, I really really appreciate it. Thank you very much in advance!
How do I Currency Convert All Values to Chosen Currency in Drop Down?
Hi all, I need a bit of help referencing a currency table and using the exchange rate provided in that table, against the relevant date, to convert the value to the currency the user has selected in a drop down on the report. When this currency from the drop down is selected I need all the values in my table ('table A') changed using this exchange rate. The maybe tricky part is that for each row in Table A (each Asset listed in the row) which contains the Asset Value, displays the native currency value as standard as per in the model view the currency column in Table A. So there are several different native currencies listed/referenced, so the native currency against the asset needs to be looked up, this is then used in the currency conversion rates table against the date against for the converstion and used to adjust the value. The added problem is that ECB only give a rate to Euros. So in all cased the amount will need to be converted to Euros and then out to the native currency again for situations where the asset's native currency is DKK , and the user has selected something like AUD for the value to be converted to. Example Data subset from EBC in past 90 Days <Cube time="2022-06-30"> <Cube currency="DKK" rate="7.4392"/> <Cube currency="AUD" rate="1.5099"/> </Cube> [EUR -> EUR] * [EUR -> DKK] [1/1.0000] * [7.4392] = 7.4392 (this one is simple as the rate matches that from the ECB extraction) [AUD -> EUR] * [EUR -> DKK] [1/1.5099] * [7.4392] = 4.9269 I've attached a sample file with this dummy data in for reference. There is an API connection to ECB to get the currency conversion rates for relevant currencies I need. It's a bit meaty this one but any help at all I'd be grateful for. Sample File
Currency Format
Dear Community, I am working on a report where the users should be able to switch between 3 different currencies. I have created a simple table (CurrencyMeasure) and the following DAX command to support a currency slicer on the report page: NET SALES = IF( HASONEVALUE(CurrencyMeasure[Currency]), SWITCH(VALUES(CurrencyMeasure[Currency]) ,"DKK",FORMAT([Net Sales (DKK)], "#,## kr.") ,"£",FORMAT(DIVIDE([Net Sales (DKK)]8.5),"£ #,##") ,"€",(FORMAT(DIVIDE([Net Sales (DKK)],"€ #,##") ,[Net Sales (DKK)])) It all works fine, however the sorting of Net Sales gets rather odd in a table as its now a string (see picture). As I would prefer to have the currency symbols in the table, is there a way to sort by highest value and not first numer in a table? Thanks in advance.
Currency Switch Measure is Mixing All Currencies at Row Level
Hello, We have a report built where a slicer can select between either US or CA. This is triggering a switch measure to select either the USD$ or CAD$ column from the DB Table: Sales $ = Switch(true(), [OnlyCanadaSelected]="No",[Sales (USD)], [OnlyCanadaSelected]="Yes",[Sales (Local)]) OnlyCanadaSelected = IF(COUNTROWS(FILTER(FlashSalesSites,FlashSalesSites[Country]<> "CA")) = 0,"Yes","No") When trouble shooting the model, I see that "Sales $" column shows local currency for both countries due to the row level context BUT the Grand Total for the matrix is totaling in USD$: I have tried multiple rewrites of the logic but cannot get around the row level. Also, I cannot understand why the Grand Total is correct? I would love some suggestions on how to correct! Thanks
line chart shows blank values
hello, i have created a measure that concatenate numerical value and string . it shows data correctly in a table visual but when i use a line chart all data become blank table: line chart: the measure i created depends on a filter, when filter is selected measure will display value without a $ sign and when filter is not selected measure will display value with $ sign. the string in my measure was to add the $ sign part to the number
