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

     

     

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

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

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

      • Angith_Nair's avatar
        Angith_Nair
        Continued Contributor
        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..