Forum Discussion
Automatically multiplying data based on individual row values
Hi,
This is the kind of calculation you should be doing in a calculated column. Assuming you have a column for Product, try this calculated column formula
=IF(Data[SKU]="2163986-5",IF(Data[Product]="Sour Apple",Data[Qty]*5,Data[Qty]),Data[Qty])
Hope this helps.
Hello Anonymous
Here is a measure that should do the trick for you:
Calculation =
SUMX (
'Table';
VALUE (
RIGHT ( 'Table'[SKU]; LEN ( 'Table'[SKU] ) - SEARCH ( "-"; 'Table'[SKU] ) )
) * 'Table'[Quantity]
)
With a SUMX the expression will be evaluated over every row and sum up the result.
In this case we determine the length of the string and determine the position of the "-".
When you subtract the length and position you know the amount of characters you need from the right side of the string.
Change the string to a number using the value function and multiply it by the quantity.
If you want to learn DAX I can highly recomend the book: The Definitive Guide to DAX by by Alberto Ferrari and Marco Russo
If you have any more questions don't hesitate to ask.
Kind regards
Joren Venema
Data & Analytics Consultant
If this reply solved your question be sure to mark this post as the solution to help others find the answer more easily.
Hey Anonymous,
Thank you, this is the level of complexity in DAX that complete elludes me at the moment.
I will try inputting this measure, I'm assuming I will need to replicate the formula for each individual SKU to get a complete number of product sold by SKU?
Also thanks for the book suggestion, I love the functionality of Power BI and really hope to master it as soon as I am able.
All the best,
Tom
You should only need this 1 measure. Basically what it does is take the number after - and multiply it by the quantity.
Im looking a little closer to to the structure of the data and it might get a little more complex when you take a look at 2148190-3 what would be the amount you want to multiply it by?