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 🙂

[Anonymous]

Hi @Harom 

Since we haven't received any response, we are closing this thread.

If you need further assistance in the future, please reach out via the Microsoft Fabric Community Forum and create a new thread. We’ll be happy to help.

Thank you for your understanding and participation

[Anonymous]

Hi @Harom 

I wanted to check if you had the opportunity to follow up on our previous conversation. If yes can you please provide the sample data so that we can provide you with the accurate and correct solution. 

Please feel free to contact us if you have any further questions. 

Thank you.

[Anonymous · Accepted solution]

Hi @Harom 
Thank you for reaching out microsoft fabric community forum.
could you please provide some sample data.so that we can look into it more effienciently.
Thank you.

[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]
)