Forum Discussion
Cap(limit) first n orders
I am doing a power bi to track money spent on promotions on a whole variety of stores, but I am experiencing an issue on the cap (limit of units with promotion). Every promotion has a limit of units assigned to each store so that there is not over expenditures.
For instance one store on a troussers promotion can give promotion to first 6 purchases and another shirt promotion may be cap to 10 first items.
My model has the following tables:
1) [Store, Promotion, Cap]: It registers all promotions available on all possible stores and the quantity limit for each pair
2) [Date, Store, Promotion, Quantity, Price, Discount]: I get the sales and "invested money on promotions" by promo and store by doing the following measure: Investment = quantity * price * discount.
With a TOPN formula I have been able to get the investment and also the cap_investment (which is the previous formula taking into account the mentioned cap). HOWEVER when I try to get the same metrics on a time graph, the TOPN formula evaluates, not per store & promo, BUT on a DAILY BASIS and the cap is never reached on a daily basis.
I would expect to see the cap_investment to be equal to the investment on a daily trend until the cap is reached and the cap_investment then turns 0 but it is not being the case since the TOPN formula evaluates on a daily basis.
I hope I made a clear explanation.
Happy to expand if needed.
Thanks!!
1 Reply
- lbendlinSuper User
Please provide sample data (with sensitive information removed) that covers your issue or question completely, in a usable format (not as a screenshot). Leave out anything not related to the issue.
If you are unsure how to do that please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216
Please show the expected outcome based on the sample data you provided.
If you want to get answers faster please refer to https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523