Forum Discussion

s15's avatar
s15
Helper III
9 years ago
Solved

Multiply a column with a fixed constant

I cannot seem to find a way to multiple a column with a constant. For example I have total sales. Now I need to set the target is 15%. The measure would loke like   Target Sales = XXX(financials...
Need Help
  • v-ljerr-msft's avatar
    v-ljerr-msft
    9 years ago

    Hi s15,

    I just firgured out the way. Instead of 0.15, it must be dividing 100/15 because I cannot seem to find a MULTIPLY function in my case. Math is awesome init'?

     

    Target Sales = SUM(financials[ Sales]) + DIVIDE(SUM(financials[ Sales]),100/15)

    Yes, there is no MULTIPLY function in DAX. The arithmetic operator  * (asterisk) is usually used for multiplication. So you should also be able to simply use the formula below to calculate the Target Sales in this scenario. :smileyhappy: 

    Target Sales = SUM(financials[ Sales]) * (1 + 0.15)

     

    Regards

vanessafvg's avatar
vanessafvg
Community Champion
9 years ago

s15 have you tested the measure i created?

 

i am getting 15% of total sales and adding it to the total sales is that not what you want?

s15's avatar
s15
Helper III
9 years ago

vanessafvg I did but this looks incorrect.

 

The current sales is 118.73M. The target sales with 15% increase is 136.5395. With Excel, it's easy like this

 

 

While the measure you provided is to divide 118.73 into 0.15 (equally mutiply 6,6 times).

 

  • s15's avatar
    s15
    Helper III
    9 years ago

    Hi vanessafvg

    I'm quite new to Power BI so please teach me if I'm wrong anyway. There are two ways to create a new measure. The 1st way is New Measure on Ribbon.

     

     

    Another way is to right click on the table at Fields panel then select New Measure.

     



    I don't think DIVIDE 0.15 works. Why? Mathematically if you divide a number into a value less than 1, the result is larger than this number.

  • vanessafvg's avatar
    vanessafvg
    Community Champion
    9 years ago

    s15

    you a hundred % correct i am being daft this morning apologies

     

     try this

     

    measure = sum(Sales[SalesAmount]) + (sum(Sales[SalesAmount])/100 * 15)

  • vanessafvg's avatar
    vanessafvg
    Community Champion
    9 years ago

    s15 can i see how you created your measure? works fine on my side