Multiply a column with a fixed constant

[s15]

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[Sales]) * 15%) + (financial[Sales])

I don't know which function to use in my case.

Thank you

[vanessafvg]

@s15 

sumx(financials[Sales]) * 15%) + (financial[Sales])

?

[s15]

@vanessafvg Thank you very much for your response. It seems not work.

I tried with yours as well. I'm not sure if there is a need of add-in or setting enabled. Note that value in Sales column is SUM

[vanessafvg]

firstl yare you creating a colum or a measure?

I am not quite sure what you trying to do, is a running total?

so  you want to add 15% to the current sales (in a column not a measure?)  ie by row and add that to the original amount?

@s15 target sales = ((sumx(financials[sales]) * 15%) + sumx(financials))

[s15]

Hi @vanessafvg

I need to create a measure which I want to multiply the current Sales with 15%. Saying my total sales revenue is 10,000 USD. But the target next year should be 11,500. However there is no such a column or value in my table. Do I have to create a new column to store the target sale value?

 

The reason I need the new measure is that I will use Gauge chart to display Sales revenue and my target sales. Right now the Sales revenue is SUM.

[vanessafvg]

@s15

you will need to change the measure names back to yours

this needs to be a measure

sales target amount = sum(Sales[SalesAmount])  + DIVIDE(SUM(Sales[SalesAmount]),0.15) 

[s15]

@vanessafvg

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)

[v-ljerr-msft · Accepted solution]

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.  

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

Regards

[s15]

Thank you @v-ljerr-msft . Math is interesting. Though Excel users love to put "15%" in the formula.

Thank you all @v-ljerr-msft and @vanessafvg

[v-ljerr-msft]

Hi @s15,

Yes, math is interesting. And Great to help!

By the way, as the problem is resolved, could you accept the corresponding reply as solution to close this thread(which could also help others who may have similar issue easily find the answer)? 

Regards

[vanessafvg]

@s15 f that works awesome

[s15]

@vanessafvg divide is not correct because the target sales is supposed to only increase 15%. It basically means that I need to top up 15% of the current sales revenue. I think measure name does not matter. It should be whatever I need right? The main thing is the caculation of multiplication.

[vanessafvg]

@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]

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

[vanessafvg]

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

[s15]

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]

@s15

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

 try this

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