Calculate price based on different conditions

[EZV12]

Hello all,

I need your help to figure out how to display a complete price list for all customers&items under different pricing methods.             

4 different pricing methods:

1) Method 1: the most simple one, the net price for Item-Customer;

2) Method 2: the gross price without discount; 

3) Method 3: the price is set for Item, all customers may order the item under the pricing method 3;

4) Method 4: a discount is set for each ItemFamily & Customer

Here below an example:

My problem: I need to display a complete price list for each item & customer. Under Method 1, it is simple, every price is there for each item/customer. But for Method 4, a caculation is needed to get the net price, it should be "the gross price under Method 2 " x (multiply by) "the discount % under Method 4".  For Method 3, I need to associate an Item with a customer, then display the price.

The order is: if we cannot find the price under Method 1, we go to check Method 3, any customer can order the item under the pricing Method 3; if there are not any price under Method 1 & 3, we should check Method 4 by using the calculation "gross price per item under Method 2" x "discount % under Method 4".

The expected output looks like below:

Item	Customer	Price
Item 1	C1	2,18
Item 2	C2	3,55
Item 3	C3	4,6
Item 4	C5	50*0,2=10
Item 5	C4	60*0,3=18
Item 7	C4	6,8
Item 8	C5	9,7
Item 6	C6	100*0,4=40

I will appreciate a lot your help to find the solution.

Thanks, and best regards

Rachel

[v-henryk-mstf · Accepted solution]

Hi @EZV12 ,

It seems to be the same question as the other post I answered, you can click on the link below to jump there to refer to my reply.

Re: DAX measure to consolidate prices under differ... - Microsoft Power BI Community

Best Regards,
Henry

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

[EZV12]

Hello @v-henryk-mstf ,

Many thanks, yes, it's the same problem. 

Best regards

Rachel

[EZV12]

Good Evening @VijayP ,

I prepared a more detailed sample pbix, here is the link :

https://1drv.ms/u/s!AnbNXrfr-X8jfM26gGNbgYZv12c?e=xU7NwP 

May you please help me to have the expected output?

Item	Customer	Price
Item 1	C1	2,18
Item 2	C2	3,55
Item 3	C3	4,6
Item 4	C5	50*0,2=10
Item 5	C4	60*0,3=18
Item 7	C4	6,8
Item 8	C5	9,7
Item 6	C6	100*0,4=40

I need a DAX formula to calculate the net price for the articles with discount code of "Item Family".  What I need is the complete price list, not the average selling price.

I hope it's clearer now.  Thanks in advance for your help.

Best regards

Rachel

[VijayP]

@EZV12 

in power query you can just filter out the Pricing method column ( filter out 2)

[EZV12]

Thanks @VijayP , however, it is not exactly what I expect, I need to have a final complete price list with each item + each customer on the same line with the corresponding price.

I have been struggling to find out the right DAX formula/measure, but so far not succeeded.

[VijayP]

@EZV12 

Challenge is in few cells customer name is missing and in few item is missing. Without any specific we cannot simply club the. What should be the logic to fill the empty cells with customer?!

[EZV12]

Hello @VijayP ,

In fact, there is another database for turnover Customer-Item-Quantity-Revenu-Average Selling Price, which can be associated with the table of pricing.  Sometimes, there are sales for certain customers & items, but the selling price is not listed by customer&Item in the table of prices, we need to search the right item family code with the right customer code to calculate the unit price for the item and the correspondant customer.

Maybe I need to create a more detailed sample pbix with different tables of data, then it will allow people to help me more easily.

Thank you a lot for your time.  I appreciate it a lot.

Best regards

Rachel

[VijayP]

@EZV12 

is the raw data in the same manner as shown in picture

[EZV12]

hello @VijayP 

Yes, in the same manner.

May you help?

[VijayP]

@EZV12 

use this attachment and in power query editor i tried the solution. Let me know if that helps

[EZV12]

hi @VijayP ,

Thanks for your help, however, how can I get the expected output like below:

I need the Item & Customer each time at the same line, and the price displayed besides either like it is or calculated, the final display should be like this.

Item	Customer	Price
Item 1	C1	2,18
Item 2	C2	3,55
Item 3	C3	4,6
Item 4	C5	50*0,2=10
Item 5	C4	60*0,3=18
Item 7	C4	6,8
Item 8	C5	9,7
Item 6	C6	100*0,4=40

Is it possible to have a DAX formula to obtain this result?

Many thanks in advance for your further help.

Rachel