SSAS (Tabular) – DAX Multi-Currency Conversion Model

Hi,

In today’s post I am venturing out in the BI arena of Microsoft’s new addition to the analysis service “Tabular”. And I can fairly see why this would have been brought in to counter the market which was being flooded with in-memory cubes and similar techniques of data-driven discovery (primarily like Qlikview, you click on data and get the associated information driving it).

And at first looks the armory around this tabular technology (which includes Power Suite for Excel: Power Query, Power Pivot, Power View & Power Map) is all very impressive and definitely opens a lot of doors to the users in their data collations across multiple sources, and some really great visualizations all in Excel !!!

Well me not talking more on the technology (as many other resources online depict the great potential of the same, in-depth) and its pro’s and con’s, So I will get back to my post which intends to help the developer facing similar challenge:

Problem:

I have a “Tabular” analysis model for sales, and I want to visualize it across multiple currency for varying time period.
The exchange rate are provided in the model for calculations.

Solution:

Now, correlating the above problem which is very easily solved in the SSAS (Multi-Dimensional model) as mentioned in my previous post:

SSAS : Muti-Currency Conversions, missing intermediate measure group ??

Things are not so straight forward in “Tabular”.

Because Many-To-Many (M2M) relationship don’t quite work as they should logically seem, hence in this solution instead of going conventional M2M, I would transform the data being loaded to the SSAS model in the form of One-To-Many (O2M).

So to solve the above problem,

1. When loading the Sales data in the model, convert all the multi-currency source sales data to the one pivoting currency (against which the exchange rate table will be populated for conversion) in this case “US Dollar”, in SQL.

If you wish to report on multi-currency reported sales data you can also keep the original measure in a separate column in tabular, with the currency key linked to the currency table termed as “FromCurrency”.

Note: The columns are grayed out in the above image, because I don’t want them to be visible as dimensions hence I have hidden them from client tools appearing them as grayed out

2. In model establish the following relationship in the table view diagram:

3. In you currency rate table punch in the following DAX measure formula to get the EOD rate for the currency

Sum of EndOfDayRate:=SUM([EndOfDayRate])

4. Now with the series of DAX formulas in the Sales table you would be able to achive the desired.

–This will provide a plain sum of the SalesAmount Column, but in excel it will populate the table for all currencies even if they dont have exchange rates
Sum of SalesAmount:=SUM([SalesAmount])

–This will represent the filtered sales amount in USD value only for which valid conversion exchange rates are available
Filtered Sales Amount:=if(isblank([Exchange Rate Applied]),BLANK(),[Sum of SalesAmount])

–This will provide the applicable exchange rate conversion value for the selections
Exchange Rate Applied:=IFERROR([Sum of EndOfDayRate],BLANK())

–This will provide the final converted sales value in the desired currency
Sales Amount Converted:=IFERROR([Sum of SalesAmount]/[Sum of EndOfDayRate],BLANK())

5. Result

To my experience with couple of transformation in the load data and with above simple DAX formulas, I was able to achieve a perfectly working multi-currency reporting model in SSAS tabular.

Which was later extended to incorporate the additional column of reported currency sales with addition of “FromCurrency” dimension.

Hope it helps !!!

Note: The above model is built on Adventureworks2014DW database downloadable from code plex site

Downloads