Calculate sum and percentage based on IF Lookup

[jwhibley]

Hi there,

I am new to DAX and am really struggling to work out how to make a measure work that looks for a particular combination of products in a column and then assigns a contribution weighting based on that combination.

The DAX query I have developed thus far looks like the below but I am unsure where I am going wrong:

measure = IF(LOOKUPVALUE(product[product_name],product[product_name]," Product A")=" Product A",
SUMX(product,
SWITCH(true(),
product[product_name] = " Product B", products[total_sell]*1+SWITCH(true(),product[product_name] = " Product A", products[total_sell]*.30),
product[product_name] = " Product A", products[total_sell]*.30 ,[total_sell])),[total_sell])

My issue occurs on the Product B line. What I would like to accomplish is when a particular deal has Product A, then add 70% of the total sell price of Product A to Product B's total sell

The actual result from my current DAX query looks something like this:

 What I need the data to look like is something more like the below, where the 70% from Product A is added to Product B's final total.

I am unsure what to do from here, any help would be really awesome

Cheers,

JJW

[parry2k]

@@jwhibley

Can you provide some sample data for solution? 

[jwhibley]

hi @parry2k

Here is a link to a sample file 

https://www.dropbox.com/s/pvb773uvxry1a1i/sample%20data.pbix?dl=0

Cheers!

[parry2k]

@jwhibley so you are only interested in product a and b, in your model you have way more products.

Please clarify.

[jwhibley]

@parry2k yes - I am only interested in Product A and Product B from the calculation perspective

[parry2k · Accepted solution]

@@jwhibley

try these two measures and tweak as it works for you.

70% of Product A = 
VAR s = CALCULATE( SUM( Sheet1[Total Sell] ), Sheet1[Product Name] = "Product A" )
RETURN s * 0.70

Add to product B

Product B with 70 % of Product A = SUM( Sheet1[Total Sell] )  +
 IF( MAX( Sheet1[Product Name] ) = "Product B",  [70% of Product A], 0 )

[jwhibley]

thank you so very much @parry2k it works great!

Cheers,

JJW

[parry2k]

Glad to hear 🙂

[parry2k]

also does that 30% + 70% contrubution is fixed.

[jwhibley]

The 30% / 70% contribution is always fixed when there is a Product A, if that makes sense