Dax formula

[HaniMoussa]

Hello,

           thanks for the time to read this problem and try to solve it.

I have two columns the first one

Product_id	product_name	category	list_price
1	Dr Marten	shoes	$   34.34
2	Roboshoees	shoes	$   54.00
3	Levi's	pants	$   22.00
4	plain white	Shirts	$   11.00
5	lumberjack	Shirts	$      6.00
6	Button Down	Shirts	$   21.00

and the second table

produt_id	discount
1	0.1
2	0
3	0
4	0
5	0
1	0.3
2	0
2	0
3	0.5
4	0
5	0
6	1
1	0
2	0
3	0
4	0
5	0
3	0

and now I want to apply the discount from Table 2 to the prices in Table 1 and create a measure, to sum up the prices for all products.

Thanks

[v-jianpeng-msft]

Hi, @HaniMoussa 

Thanks @Sergii24 . Based on your description, I used the following sample data:

I created a calculated column as shown in the image below:

Newprice =
VAR _currentid = 'Table'[Product_id]
VAR _discount =
    CALCULATE (
        SUM ( Table1[discount] ),
        FILTER ( 'Table1', 'Table1'[produt_id] = _currentid )
    )
RETURN
    SWITCH ( _discount, 0, 'Table'[list_price], 'Table'[list_price] * _discount )

I use the following measure to find the total selling price of all products:

Total price = CALCULATE(SUM('Table'[Newprice]),ALL('Table'))

I've provided the PBIX file used this time below.

How to Get Your Question Answered Quickly

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards

Jianpeng Li

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

[HaniMoussa]

now in Table 1, I made sales operations with different discount ratios for each client buying a dedicated product.

I want to use the prices, apply these ratios, and then calculate the sum (for both before and after discount prices).

Thanks for your support

[v-jianpeng-msft]

Hi, @HaniMoussa 

Thank you very much for your reply. Can you give us an indication of the output you are expecting based on the sample data you provided?e.g. when Product_id=1, there are multiple discounts in table 2, are they summed and then applied to table 1?

Best Regards

Jianpeng Li

[HaniMoussa]

order_id	produt_id	discount
1	1	0.1
2	2	0
3	3	0
4	4	0
5	5	0
6	1	0.3
6	2	0
7	2	0
8	3	0.5
9	4	0
10	5	0
11	6	1
12	1	0
13	2	0
14	3	0
15	4	0
16	5	0
2	3	0

there is table one for the deals and the applied discount

id	product_name	category	list_price
1	Dr Marten	shoes	$   34.34
2	Roboshoees	shoes	$   54.00
3	Levi's	pants	$   22.00
4	plain white	Shirts	$   11.00
5	lumberjack	Shirts	$      6.00
6	Button Down	Shirts	$   21.00

this is the second table for the prices of items

what I search for if this applicable is "  create a price column in table one mapping the price from table two to calculate the total income and another column with discounted price to compare both outputs"

[v-jianpeng-msft · Accepted solution]

Hi, @HaniMoussa 

Thank you very much for your reply. Based on your description, you need to map the price in table1, here's how I did it:

Price =
CALCULATE (
    MAX ( 'Table'[list_price] ),
    FILTER ( 'Table', 'Table'[Product_id] = 'Table1'[produt_id] )
)

New price =
IF (
    'Table1'[discount] <> 0,
    CALCULATE ( MAX ( 'Table1'[discount] ) * MAX ( 'Table1'[Price] ) ),
    'Table1'[Price]
)

Here are the results:

Sum price discount = SUM(Table1[New price]) 
Sum Price no discount = SUM(Table1[Price]) 

Use these two measures in the table visual:

I've provided the PBIX file used this time below.

How to Get Your Question Answered Quickly

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards

Jianpeng Li

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

[HaniMoussa]

Thanks for your support, done

[Sergii24]

Hi @HaniMoussa, I see that you have multiple discounts for the same product in the second table (example product ID 1). So what value do you want to apply: 0.1, 0.3 or 0 and based on what criteria?

The general suggestion I'd give you is to create a relatinship between these table (or use a bridge table that contains distinct values of Product ID). In that way, by using Product ID from this new table you can get the access to all prices and discounts of related product.

Good luck!