Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
Solved! Go to 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.
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
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"
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.
Thanks for your support, done
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!
User | Count |
---|---|
96 | |
87 | |
78 | |
72 | |
69 |
User | Count |
---|---|
113 | |
105 | |
84 | |
65 | |
64 |