Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
I have two tables with a common column 'category':
A: category, product, price
B: category, discount_level, discount_pct
Table A has multiple products per category and table B has multiple discount levels per category, so the relationship on category is many-many.
Question: How can I calculate price*discount_pct based on a discount level selected by the user in a slicer?
Note: I have a solution but it is too slow. The solution is to transpose the discount levels into columns, which makese the category in table B unique, then use SWITCH to choose the correct column. I would like to avoid transpose to see if I can improve the performance. (I believe the performance is slow using transpose because it must calculate discounted prices at every discount level first, then select the correct version according to the user selection using SWITCH.)
Thanks but the discount percentages are different for each category so a parameter won't work (unless I'm missing something).
Hi, @Bill_NYC
Not sure what you want.
Does the following formula not work in your many-many table model?
Measure = MAX('Table A'[price])*MAX('Table B'[discount_pct])
Best Regards,
Community Support Team _ Eason
Hi:
You could do something like creating a new table to be the brige table allowing for relatinoship measures.
=DISTINCT(UNION(ALL(TableA[Category]), ALL(TableB[Category]).
This new table would join the other two tables with category.
I hope this helps.
I had tried using a bridge table but that didn't solve it for me. (I still have many-many until the user selects a discount level.)
Hi Bill:
If you supply sample data it can be figured out. Are your discounts for each category static or rolling based upon time intervals.
E.G. of Configuration Table
Category Discount % Start End
Bikes .10 1-01-2022 2-01-2022
Bike .07 2-15-2022 2-28-2022
Biles .05 3-01-2022
Toys .10 2-01-2022 4-15-2022 etc.
etc.
If this is the case you'd need a separate config table to run logic thru. It's hard to guess without seeing some data and expected results.
Hi:
I think you are talking about make a New Parameter. And the values in the new parameter are the discount amounts. (.05, .1, etc - whatever ones you have).
I will attach a file showing how to implement this. In the attachment a user can select the rate of expected sales growth (the parameter) and that value is used in a measure. Page three of this report shows the parameter "Sales2" and the parameter measure "Sales Value" is used in the final measure "Goal". This same approach will work for you based on discounts. If you go to Modeling>New Parameter you will see the screenshot below. Once you key in your discount values a new slicer wil appear (for your users) and then you tie your measures into this parameter SELECTEDVALUE.
It wouldn't hurt to watch a 3-5 minute Youtube on Power BI Desktop New Parameter for extra background. I hope this input solves your question. You will notice there is a data model behind the scenes suppporting the analysis.
https://drive.google.com/file/d/1VfghfltY9bseg0W-x4pyJeNLTXau-MCs/view?usp=sharing
If this still sounds fuzzy, please post some example data for your tables and I will attempt to do it for you.
Thanks
User | Count |
---|---|
47 | |
26 | |
22 | |
17 | |
15 |
User | Count |
---|---|
55 | |
34 | |
18 | |
17 | |
15 |