Slicers in service of calculations

Slicers are graphic objects that are used as an interactive filters for filtering reports in Pivot and Power Pivot reports. In combination with certain DAX functions they can be used for setting the parameters of the calculation. The following recipe will show you how to do a sales report in which, according to chosen exchange rate, you can see results in different currencies. To test a status of a slicer will be used function HASONEVALUE.

To demonstrate how slicers can filter Power Pivot reports  we’ll be using example that tracks results of a retail chain business in a certain time period. In the Model we have added and connected tables Artikli (Items), Lokacije (Locations) and Transakcije (Transactions). In the model we add another table, which we might call Valute (Currencies). It consists of two columns. In the first is a short code of the currency, and the second is actual exchange rate. This table will be used to create slicers and is not linked to the other tables in the Model. First, let’s create a measure that calculates sum of all amounts:

UkupnaProdaja:=SUM(Transakcije[IZNOS])

Now we need to create another measure. Its goal is to show total sales in chosen currency. To do this we’ll use function HASONEVALUE. Our aim is to use this function in order to check whether slicer is active and a currency chosen. If this condition is met, formula for calculation is divider of measures UkupnaProdaja and exchange rate from the table. In not, it’s the value of UkupnaProdaja. Further, we’ll use function IF to create an expression:

UkupnaProdajaVAL:=
IF(HASONEVALUE(Valute[VALUTA]),
[UkupnaProdaja]/VALUES(Valute[KURS]),[UkupnaProdaja])

To calculate a divider we have also used DAX function VALUES. For given column it returns a distinct (numerical) value as a result. This way formula won’t return an error. At the end, let’s make a Power Pivot report. We’ll drag KLASIFIKACIJA (CLASSIFICATION) in Rows, and UkuplaProdajaVAL in Values. Further, we’ll create a slicer by column VALUTA from table Valute. Each time we chose a different currency on slicer the report will be updated.