Hi, I want to find the average discount rate across all orders for a business.
I have tried Avg(Discount Percentage), but that just returns the average of all of our discount rates. So if we have a 20% offer, and a 10% offer, it just returns 15%.
Ideally I want a weighted average based on the number of times a discount rate has been used.
The key identifier is Special Offer Dimension Key is stored in a fact table, and the Discount Percentage is stored in a dimension table, corresponding to that key.
Please help, thank you.