Calculation based on 2 or more variables (slicer/parameter?)

[Harom]

Hi,

I'm new to Power BI, but not with data. I've been searching the web and this forum for the following, but without success sofar:

I'm looking for a way to get a calculation Sales Period Currency based on two variables:

1. Currency selection (EUR/USD): I have two columns in my data set Sales Value EUR and Sales Value USD
2. Period selection: I have several columns with Period Indicators (YTD, L12M,Etc for current year and last year). This field shows a 1 when the date for this row falls under the period condition and 0 (or maybe NULL) when this is not the case

The idea is to

 - first create a field Sales Value Currency, based on a (parameter/slicer) selection 

 - Create a field called Period Selection, based on a (parameter/slicer) selection

 - And finally create a Sales Value CUR Period = SUM(Sales Value Currency * Period Selection)

Combining this would make the calculation very flexibel, with a minimum of steps. Next I would like to re-use the same paramter/slicer for the same logic for Quanty and Margin calculations

Other ideas, or existing solutions are more than welcome 🙂

[Harom]

@bhanu_gautam 
Thank you for your quick reply!

When I go to Modeling > New Parameter , I get two options: Numeric Range and Fields. In both cases I'm not able to create my own two options.

[HarishKM]

@Harom Hey,
you can create your all measure then create a field parameter for the same as below image.

expected output as below.

Thanks
Harish M
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly and give Kudos if helped you resolve your query

[HarishKM]

@Harom Hey,
try below things

1st table

CurrencySelection =
DATATABLE("Currency", STRING, {
{"USD"},
{"EUR"},
{"GBP"}
})

2nd table 

PeriodSelection =
DATATABLE("Period", STRING, {
{"MTD"},
{"YTD"},
{"QTD"},
{"Full Year"},
{"PY MTD"},
{"PY YTD"}
})

once you have above 2 disconnected (Newly created table) then you have create below measure

SelectedSales =
VAR SelectedCurrency = SELECTEDVALUE(CurrencySelection[Currency])
VAR SelectedPeriod = SELECTEDVALUE(PeriodSelection[Period])

-- Step 1: Apply currency conversion logic (example rate logic)
VAR SalesInCurrency =
SWITCH(
SelectedCurrency,
"USD", [Sales_USD],
"EUR", [Sales_EUR],
"GBP", [Sales_GBP],
[Sales_USD] -- default
)

-- Step 2: we have to write a period logic now
RETURN
SWITCH(
SelectedPeriod,
"MTD", CALCULATE(SalesInCurrency, DATESMTD(DateTable[Date])),
"YTD", CALCULATE(SalesInCurrency, DATESYTD(DateTable[Date])),
"QTD", CALCULATE(SalesInCurrency, DATESQTD(DateTable[Date])),
"Full Year", CALCULATE(SalesInCurrency, YEAR(DateTable[Date]) = YEAR(TODAY())),
"PY MTD", CALCULATE(SalesInCurrency, DATESMTD(SAMEPERIODLASTYEAR(DateTable[Date]))),
"PY YTD", CALCULATE(SalesInCurrency, DATESYTD(SAMEPERIODLASTYEAR(DateTable[Date]))),
SalesInCurrency -- default 
)

above solution is fully scaleble for future.

Thanks
Harish M
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly and give Kudos if helped you resolve your query

[Harom]

@HarishKM 
I've got the Table and selected value part working. When I want to apply this in for example the VAR SalesinCurrency the way you described (I've added a New Column with the calculation), there is no result in the column.

-- Step 1: Apply currency conversion logic (example rate logic)
VAR SalesInCurrency =
SWITCH(
SelectedCurrency,
"USD", [Sales_USD],
"EUR", [Sales_EUR],
"GBP", [Sales_GBP],
[Sales_USD] -- default
)

My logic:

Sales Value Currency =

SWITCH('Par Currency'[Currency Selected],

"EUR", Query2[Amount Value],

"USD", Query2[Amount Value USD])

[Harom]

@HarishKM That could be an option, but we want to combine currencies and periods and with preferably one parameter/slicer for each variable, to keep it maintainable.
3 currencies x 6 different periods for at least 4/5 measures

[bhanu_gautam]

@Harom , You can do that like

Create a Currency Selection Table:

Go to the "Home" tab and select "Enter Data".
Create a table with a single column named "Currency" and add the values "EUR" and "USD".
Name this table "CurrencySelection".
Create a Period Selection Table:

Similarly, create another table for period selection with the desired periods (e.g., YTD, L12M, etc.).
Name this table "PeriodSelection".
Create Relationships:

Ensure that these tables are not related to your main data table. They will be used solely for slicers.
Create Measures for Currency and Period Selection:

   Selected Currency =

   SELECTEDVALUE(CurrencySelection[Currency])

and create similar for other and use them in field parameter

[Harom]

@bhanu_gautam the good news, the solution with the tables works and I managed to get all columns and measures working without errors showing, the bad news is that the new created columns stay empty. When I put the slicer in the report and a  table with the calculation, it works:

Some other screenshots:

[bhanu_gautam]

@Harom Go to the Modeling tab and select "New Parameter".
Name it "Currency Selection" and add the values "EUR" and "USD".
This will create a slicer that allows you to select between EUR and USD.

Similarly, create another parameter for period selection with the desired periods (e.g., YTD, L12M, etc.).

Create a Calculated Column for Sales Value Currency:

DAX
Sales Value Currency =
SWITCH(
[Currency Selection],
"EUR", [Sales Value EUR],
"USD", [Sales Value USD]
)

DAX
Period Selection =
SWITCH(
[Period Selection],
"YTD", [YTD Indicator],
"L12M", [L12M Indicator],
-- Add other periods as needed
)

Create the Final Measure for Sales Value CUR Period

DAX
Sales Value CUR Period =
SUMX(
YourTable,
[Sales Value Currency] * [Period Selection]
)

Reuse the Parameters for Quantity and Margin Calculations:

DAX
Quantity Value Currency =
SWITCH(
[Currency Selection],
"EUR", [Quantity Value EUR],
"USD", [Quantity Value USD]
)

Quantity Value CUR Period =
SUMX(
YourTable,
[Quantity Value Currency] * [Period Selection]
)

DAX
Margin Value Currency =
SWITCH(
[Currency Selection],
"EUR", [Margin Value EUR],
"USD", [Margin Value USD]
)

Margin Value CUR Period =
SUMX(
YourTable,
[Margin Value Currency] * [Period Selection]
)