Forum Discussion
Dax formula
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
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.
7 Replies
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!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.
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
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
