Create a dax formula that multiply an amount with a pourcent

[Shakaze]

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

[Kishore_KVN · Accepted solution]

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!!

[Anonymous · Accepted solution]

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

[Kishore_KVN · Accepted solution]

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!!