Cost calculation for products with bulk buying rules

[msfer]

Hi there 

I have the following data set where Product C and D doesn't have unit cost due to the bulk buying rules: 

	Qty	Unit Cost	Total Cost
A	5	2	10
B	4	3	12
C	10
D	8
E	6	5	30
C	5
D	7
C	5
D	10
Total	60

This is my current formula which doesn't work, becuase it's a column the 2nd and 4th lines don't work. 

Cost1 = Switch( True(),

[Product]= "C" && [Qty] <= 5, [Qty]*5 ,

[Product]= "C",5*5 + ([Qty]-5)*2,

[Product]= "D" && [Qty] <= 2, [Qty]*3 ,

[Product]= "D", 2*5 + ([Qty]-2)*4,

[Qty]*[Unit Cost]

How would I go about fixing the [Qty] in to this formula to take in to account the sum of all Products for C and D?

I have tried bringing Qty in as a measure Sum([Qty]) which didn't work.
and also created this measure, which works perfectly except for the report table total (becasue it's a measure). So I am stuck there too. 

 Cost2 =

IF([Total Qty] <= 5 && MAX('Product'[Product Name])="C", [Total Qty]*5 ,

IF(MAX('Product'[Product Name])="C", 5*5 + ([Total Qty]-5)*2,

IF([Total Qty]<= 2 && MAX('Product'[Product Name])="D", [Total Qty]*3,

IF(MAX('Product'[Product Name])="D", 2*5 + ([Total Qty]-2)*4,

[Total Qty]*MAX('Product'[Unit Cost])

))))

Hoping someone can shed some light on this. 

Thank you in advance.

[v-zhangti]

Hi, @msfer 

What are your expectations?

Please provide more information.

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[msfer]

Hi v-zhangti

Thank you so much for your reply! 

The probelm is, the [Qty] needs to be taken in to account at the over all amount, not at a row level. That's why the measure calc (as oppoed to the column calc) works. 

I've created a PBIX, how do I attach it here. 

Basically: 

[v-zhangti · Accepted solution]

Hi, @msfer 

You can try the following methods.

Measure = 
Var _table=SUMMARIZE('Product','Product'[Product Name],"Cost2",[Cost2])
Return
SUMX(_table,[Cost2])

Is this the result you expect?

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[msfer]

This worked perfectly. Thank you for your help @v-zhangti