With this set up, the current filter context will pull back only one cross rate because there’s only one currency selected at a time. This is why CurrencySelected has been set up using a ticker. To look up the currency selected, we’re using the Ticker from the same table. In order to know the rate we need to give, we’re going to look up the Crossrate in the FactCurrencyRates table. But for this example, we’re sticking with Total Sales. It could be Profit or Margin, among others. This dimension could be changed to anything. We’re also including the measure we want to convert. These are the previous two measures we’ve just created. We want to know where we are in the current date so we have a variable defined as Date. Variables are used because they clean up the code and make it easier to debug. This is the final measure to get the converted sales. Let’s now go to the to the file table and determine how to put these together to get a converted value. We also want to know where we are, which is again referring to our status in the current filter context. Instead, it means the date for the current filter context. And when we say current date, we’re not referring to the date today. Now, the current date is also important for this technique. This can also be made to show a blank or an error. The default currency will depend on the business requirements. If a user selects more than one currency, it’s going to automatically use the default which is USD. We need to have two additional measures to help us figure out the currency the user selected. Add The Currency Selected And Current Date Measures We’re going to use the full name of the currencies. Add A Slicerįor the next step, we’re going to add a slicer from our dimension currency rates tables. The table is simply showing the Date and Total Sales: 2. For this demo, we don’t have a Total Sales column so, we’re using SUMX to iterate the FactSales and get the Quantity multiplied by the Unit Price. The first step is to create a basic measure. It’s a more convenient arrangement compared to making it alphabetical. USD is the most commonly used so it’s placed at the top. The sorting order is how these currencies will be viewed in a slicer. They will only serve as columns that allow us to format the currency appropriately using only one measure. The leading text and format columns aren’t going to be used in this demo. There’s also a column containing the full name of the ticker in case some users are unfamiliar with the abbreviations. Now, if we take a look at the DimCurrencyRates table, we can see that the ticker is the unique value that will be related to the FactCurrencyRates table. The DimCurrencyRates table is related to the FactCurrencyRates table based on the currency rate ticker. This will be used as a filter for a matrix or table. We can see that the two fact tables are related to the DimDate table from the date column. We have two Fact Tables – a Sales table and a Currency Rates table. The Data Model From World Wide ImportersĪgain, this comes from the World Wide Importers database. We’re also going to assume that all initial Sales made are in USD. But for this demo, we’re just going to say that we want to convert it on the day the sale took place. There are multiple ways of viewing this which depend on the varying businesses’ requirements. We could take the average rate of the month or choose not to convert it to a specific currency until the end of the month. Next, we’re also going to assume that Sales (the measure used in this instance) are converted on the date the sale takes place. We can create this based on our fact currency rates and then add different fields to it. The second assumption is that we have a Currency Rate Dimension Table. Even if there’s no actual rate on those specific days, it does not mean that a rate does not exist. These are the basic assumptions applied in this example:įor the first assumption, this could happen if our source data doesn’t have data for the weekends or holidays. The Data Model From World Wide Importers.
0 Comments
Leave a Reply. |
Details
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |