cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

## Create a dax formula that multiply an amount with a pourcent

Hello everyone,

I'm creating what we called a P&L in accounting.

I have numbers in 2021 and 2022.

My goal is to create a slicer with a pourcent (let's say : 1% 2% 5% 10%).

I would like to had 2-3 columns in my table ( 2023-2024-2025) to show the evolution of numbers with the pourcent choosen in the slicer (the formula would just be N*1.01 for 1% as example).

Does someone have any idea how i could create this formula?

Thanks

2 ACCEPTED SOLUTIONS
Super User

Hello @Shakaze create a small table with a column name as Percentage_Column and values will be 1%, 2%, 5% and 10% and drag them into a slicer.

Then create a measure to do the calculation.

Measure looks as below:

SWITCH(TRUE(),

SELECTEDVALUE(Percentage_Column) = "1%", SUMX(Table,'Table'[N]*1.01),

SELECTEDVALUE(Percentage_Column) = "2%", SUMX(Table,'Table'[N]*XYZ),

SELECTEDVALUE(Percentage_Column) = "5%", SUMX(Table,'Table'[N]*ABC),

SELECTEDVALUE(Percentage_Column) = "10%", SUMX(Table,'Table'[N]*BCD),

BLANK())

If this post helps, then please consider accepting it as the solution to help other members find it more quickly. Thank You!!

Resolver II

Hi.

Here is a solution for your problem.

You want to multiply a column (for example "2022") with a value the user select in a slicer.

Here is a solution that achives your goal!
Please mark as solution if it solves your problem, much appreciated 🙂

Result:

Steps for reproducing the result, e.g. calculated columns based on value user select in slicer:

0) Generate data

In the model pane, create table for sample data:

``````Data =
DATATABLE (
"Value", STRING,
"2022", DOUBLE,
{
{ "Tonnage", "21295" },
{ "Air Waybill", "13280" },
{ "Freight Sales", "103851" },
{ "Gross Margin", "7071" }
})``````

, as is shown in snippet below:

1) create a table with values used in the slicer
This values is what the user can select from in the slicer:

``````Percentage =
DATATABLE (
"Percentage", INTEGER,
{
{ 1},
{ 2},
{ 5}
})``````

2) Add measures that you want to show in your visual/table based on user selection:

Here I assume you want to derive/calculate 2023 and 2024 based on 2022 and the user selected percentage.

``2023 = MAX(Data[2022]) *(1+[Selected percentage]/100)``
``2024 = MAX(Data[2022]) *(1+[Selected percentage]/100)^2``

3) Add slicer that user should select percentage from
As shown below:

You can change the slicer to require single selection

4) Add your measures 2023 and 2024, they are calculated based on selection in slicer

👾Done!

Does this solve your problem @Shakaze ?
If so, mark as a solution.

Kind regards

2 REPLIES 2
Resolver II

Hi.

Here is a solution for your problem.

You want to multiply a column (for example "2022") with a value the user select in a slicer.

Here is a solution that achives your goal!
Please mark as solution if it solves your problem, much appreciated 🙂

Result:

Steps for reproducing the result, e.g. calculated columns based on value user select in slicer:

0) Generate data

In the model pane, create table for sample data:

``````Data =
DATATABLE (
"Value", STRING,
"2022", DOUBLE,
{
{ "Tonnage", "21295" },
{ "Air Waybill", "13280" },
{ "Freight Sales", "103851" },
{ "Gross Margin", "7071" }
})``````

, as is shown in snippet below:

1) create a table with values used in the slicer
This values is what the user can select from in the slicer:

``````Percentage =
DATATABLE (
"Percentage", INTEGER,
{
{ 1},
{ 2},
{ 5}
})``````

2) Add measures that you want to show in your visual/table based on user selection:

Here I assume you want to derive/calculate 2023 and 2024 based on 2022 and the user selected percentage.

``2023 = MAX(Data[2022]) *(1+[Selected percentage]/100)``
``2024 = MAX(Data[2022]) *(1+[Selected percentage]/100)^2``

3) Add slicer that user should select percentage from
As shown below:

You can change the slicer to require single selection

4) Add your measures 2023 and 2024, they are calculated based on selection in slicer

👾Done!

Does this solve your problem @Shakaze ?
If so, mark as a solution.

Kind regards

Super User

Hello @Shakaze create a small table with a column name as Percentage_Column and values will be 1%, 2%, 5% and 10% and drag them into a slicer.

Then create a measure to do the calculation.

Measure looks as below:

SWITCH(TRUE(),

SELECTEDVALUE(Percentage_Column) = "1%", SUMX(Table,'Table'[N]*1.01),

SELECTEDVALUE(Percentage_Column) = "2%", SUMX(Table,'Table'[N]*XYZ),

SELECTEDVALUE(Percentage_Column) = "5%", SUMX(Table,'Table'[N]*ABC),

SELECTEDVALUE(Percentage_Column) = "10%", SUMX(Table,'Table'[N]*BCD),

BLANK())

If this post helps, then please consider accepting it as the solution to help other members find it more quickly. Thank You!!

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.