Automatically multiplying data based on individual row values

[Anonymous]

Hi All,

I'm pretty new to Power Bi and still getting to grips with DAX expressions and the like, hoping for some guidance on something that's got me pulling my hair out ATM!

Here is a selection of the data that interests me, I would like to have an automatic way of finding out how many prodcuts are in each SKU (in this case there is only Pomegranate and Sour Apple)

I basically need 2 columns added for Sour Apple and Pomegranate and a calculation that gives me (e.g. IF :SKU = 2163986-5, THEN : Sour Apple = quantity *5)

As I say, i'm not familiar with DAX yet (any good resource recomendations would also be great) so I'm hoping someone can help out with what expression would work well for this 🙂

Cheers,

Tom

[Ashish_Mathur · Accepted solution]

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.

[Anonymous]

Hi @Ashish_Mathur ,

Thanks this seems to be working, the only problem I'm having is when I want to add a 2nd, 3rd, 4th etc. expression to the column they seem to interfere with each other (most likely a novice mistake on my part) Here is how I have structured it:

TotalSA = IF(LineItems[Sku]="2163986-5",LineItems[Quantity]*5)

= IF(LineItems[Sku]="2148190-2",LineItems[Quantity]*1)

Is this because I have 2 expressions interering with one another, should they instead be built into the same line?

Thanks for your help so far 🙂

Tom

[Ashish_Mathur]

Hi,

Does this work?

=IF(LineItems[Sku]="2163986-5",LineItems[Quantity]*5,LineItems[Quantity])

Hope this helps.

[v-frfei-msft]

Hi @Anonymous,

I have created a sample for your reference, please use the measures as below to work on it.

Measure = 
VAR sku =
    MAX ( 'Table'[SKU] )
VAR leng =
    SEARCH ( "-", sku, 1, BLANK () )
VAR lenall =
    LEN ( sku )
VAR num =
    RIGHT ( sku, lenall - leng )
VAR qu =
    SUM ( 'Table'[Quantity] )
var a =  IF ( leng <> BLANK (), num * qu, qu )
RETURN
   a

Measure 2 = SUMX('Table',[Measure])

For more details, please check the pbix as attached.

[Anonymous]

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. 

[Anonymous]

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

[Anonymous]

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?