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