Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
s15
Helper III
Helper III

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

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

Thank you

1 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. Smiley Happy 

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

 

Regards

View solution in original post

16 REPLIES 16
vanessafvg
Super User
Super User

@s15 

 

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

 

 

?





If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!




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

 

FOMULAR.PNG

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

 

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





If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!




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?

 

sales01.PNG 

 

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.

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





If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!




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

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

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

 

Regards

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

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)? Smiley Happy

 

Regards

@s15 f that works awesome

 

 





If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!




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

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





If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!




@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

 

sales03.PNG

 

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

 

sales02.PNG

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





If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!




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.

 

measure01.png

 

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

 

measure02.png

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.

@s15

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

 

 try this

 

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





If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!




Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.