Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello,
I have a table that contains various products, all identified by a unique Product ID. This table can be filtered using slicers, by either Product ID, or Product Description. However, some items may have the same description, although the Product ID remains unique. I have costs for each product showing in each row of the table, and I need to add a formula to show a discounted price if more than 1 unique Product ID is contained within the table (based on the filtered slicer selection).
For example, if someone uses the slicers to filter down the table to 1 product, they will see the product attributes in separate columns, followed by a column for the full price of that item. If they select multiple products via the slicers, then a set % discount needs to be applied to each row, and shown in its own column along with a grand total.
I've been able to use a measure to add a column showing the discount % to apply as follows:
Product ID | Product Description | Original Price | Multiple Part Discount | Discounted Price |
P100 | Part 100 | $100.00 | 0% | $100.00 |
Total: | $100.00 | 0% | $100.00 |
More than 1 unique Product ID selected:
Product ID | Product Description | Original Price | Multiple Part Discount | Discounted Price |
P100 | Spare Part | $100.00 | 10% | $90.00 |
P101 | Another Spare Part | $125.00 | 10% | $112.50 |
P102 | Some Other Spare Part | $50.00 | 10% | $45.00 |
P103 | Spare Part | $75.00 | 10% | $67.50 |
Total: | $350.00 | 10% | $315.00 |
Solved! Go to Solution.
Thanks for the feedback, I hadn't thought to aggregate the price for some reason. However, I did try that, and it made all the line-level calculations correct, but was still using the lowest price when calculating the total row, which doesn't work for this usage. What I ended up doing is using a SUM instead of the MIN you included below, and that took care of both the line-level details as well as the grand totals.
Thanks for the help!
Hi,
Does this measure work?
=MIN('ProductPricing'[Original Price])*(1-[DiscountPct])
Hope this helps.
Thanks for the feedback, I hadn't thought to aggregate the price for some reason. However, I did try that, and it made all the line-level calculations correct, but was still using the lowest price when calculating the total row, which doesn't work for this usage. What I ended up doing is using a SUM instead of the MIN you included below, and that took care of both the line-level details as well as the grand totals.
Thanks for the help!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
107 | |
93 | |
77 | |
65 | |
52 |
User | Count |
---|---|
147 | |
106 | |
104 | |
87 | |
61 |