Forum Discussion
Calculation based on 2 or more variables (slicer/parameter?)
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:
- Currency selection (EUR/USD): I have two columns in my data set Sales Value EUR and Sales Value USD
- 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 🙂
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.
11 Replies
- bhanu_gautam
Super User
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]
) 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.- 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 parameterbhanu_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:
- 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
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.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.
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
