Sales overview in selected currency

When creating business reports, there is often a requirement that they should be displayed in multiple currencies, that is, the user can choose the currency in which a report is displayed. This can be achieved in several ways and one of them, very elegant, is with the help of the LAMBDA function. In this “recipe” you will find out how you can use it to display sales reports in different currencies.

Based on the Transakcije (Transactions) table, which contains data on sales transactions in a certain period of time, we have created a simple report that shows the total sales (IZNOS) in relation to the classification (KLASIFIKACIJA) in Serbian dinars (RSD). To generate a dynamic classification list, the following formula is used:

=SORT(UNIQUE(Transakcije[KLASIFIKACIJA]))

We calculated the sales values in relation to the individual elements of the classification with the help of the formula:

=SUMIF(Transakcije[KLASIFIKACIJA], A6#, Transakcije[IZNOS])

As you can see, in the formula, we refer to the columns in the Transactions table.

To the right of the table in which we see the values of sales in relation to the classification, we have added a table that shows the exchange rates for RSD, EUR and USD. In practice, you’ll create it in another worksheet, and it’s shown here to help you understand how the LAMBDA function works. In cell B3, with the help  of the Data Validation option, we set the currency values to be selected from the list.

We’ll add an identical blank table to show sales, in the selected currency, relative to the classification. In the list (B3) let us select the currency EUR.

The value of sales in the given currency is calculated by dividing the value of sales in RSD by the exchange rate taken from the auxiliary table. This is calculated using the formula:

=B6/LAMBDA(x,VLOOKUP(x,$D$6:$E$8, 2, FALSE))($B$3)

The LAMBDA function has two arguments: the variable x and the expression where it is used. In the expression, we used the VLOOKUP function, which, based on x (the given currency), finds the value of the currency in the table, and then returns another column – the exchange rate. At the end of the formula, in parentheses, we specified that x is taken from cell $B$3. In practice, this means that we divide the value of sales by the exchange rate, and which exchange rate will be used depends on what we have chosen in cell B3.

Now you can choose different currencies and you will get respectively different sales values unless you choose RSD. In this case, the sales value will be the same in both tables.