Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Multiply with difference columns based on a value

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?

Need Help
  • Angith_Nair's avatar
    Angith_Nair
    5 years ago

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

     

     

9 Replies

  • Angith_Nair's avatar
    Angith_Nair
    Continued Contributor
    5 years ago

    Hi Anonymous ,

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

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

       like this!

      • Angith_Nair's avatar
        Angith_Nair
        Continued Contributor
        5 years ago

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

  • Smauro's avatar
    Smauro
    Solution Sage
    5 years ago

    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

  • Syndicate_Admin's avatar
    Syndicate_Admin
    Administrator
    5 years ago

    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