- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
Solved! Go to Solution.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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!
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
@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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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!
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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!
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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)
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
@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!
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
@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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
@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!
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
@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).
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
@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!
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
Join us at the Microsoft Fabric Community Conference
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
Microsoft Fabric Community Conference 2025
Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.
Subject | Author | Posted | |
---|---|---|---|
03-04-2024 10:23 AM | |||
06-06-2024 04:39 AM | |||
07-30-2024 12:17 PM | |||
01-11-2023 12:09 AM | |||
04-05-2023 07:40 AM |
User | Count |
---|---|
126 | |
79 | |
60 | |
58 | |
44 |
User | Count |
---|---|
181 | |
121 | |
82 | |
70 | |
54 |