Multiply with difference columns based on a value

[Anonymous]

I have a query which need to multiply wiith different columns based on a column "Duration". Formula like this:

when duration is 1, I need to multiply commission amount, exchange rate and 1st year.
when duration is 2, multiply commission amount, exchange rate and 2nd year.
when duration is 3, multiply commission amount, exchange rate and 3rd year.
when duration is 4, multiply commission amount, exchange rate and 4th year.
etc...

Is there any formula or method could help?

[Syndicate_Admin]

Hi @KLee1024 

You could try adding this as a step, it skips unpivoting and just selects the correct column:

= let

    YearColumns = List.Select(

        Table.ColumnNames(#"Removed Columns"),

        each Text.EndsWith(_, "year"))

in

    Table.AddColumn(#"Removed Columns", "New Column",

    each [Commission Amount] * [#"Exchange Rate (History)"] *

    Record.FieldOrDefault(_, (List.Select(YearColumns,

    (t) => Number.From(Text.Select(t, {"0".."9"})) = [Duration] ){0}?)??"" ),

    type number)

Cheers

[Smauro]

Hi @Anonymous 

You could try adding this as a step, it skips unpivoting and just selects the correct column:

= let

    YearColumns = List.Select(

        Table.ColumnNames(#"Removed Columns"),

        each Text.EndsWith(_, "year"))

in

    Table.AddColumn(#"Removed Columns", "New Column",

    each [Commission Amount] * [#"Exchange Rate (History)"] *

    Record.FieldOrDefault(_, (List.Select(YearColumns,

    (t) => Number.From(Text.Select(t, {"0".."9"})) = [Duration] ){0}?)??"" ),

    type number)

Cheers

[Angith_Nair]

Hi @Anonymous ,

Could you please provide some sample data so as to understand it more clearly..? 

[Anonymous]

 like this!

[Angith_Nair]

Try to unpivot the year columns in Power Query level. After unpivoting the table will look like this..

Then in Power BI try to create a calculated column like this:

Column =
IF (
    SELECTEDVALUE ( 'Table'[Duration] ) = SELECTEDVALUE ( 'Table'[Year] ),
    'Table'[Commission Amount] * 'Table'[Exchange Rate] * 'Table'[Values],
    0
)

which will give you the result...

If this helps, kindly mark this as a solution. Appreciate with Kuddos. Thank You..

[Anonymous]

Thank you so much for your help! But I can't use IF in the formula...

[Anonymous]

That's work! Really thankssss!!!

[Angith_Nair · Accepted solution]

I have made a calculated column in the Power BI level. If you want to make this in Power Query then write the code like this..

The result will be something like this..

[Anonymous]

Hi sorry, can I have one more question?

As I was merged table for the exchange rate which have few months data when I unpivot the year the result will calculated all months of exchange rate for 1 case but I only want when 1 case appear then calculate the correct formula [amount * exchange rate * value].

I don't know do you understand me....haha....but thanks anyway!