Forum Discussion
Calculating daily retail budgets
I am attempting to calculate budgets for retail stores for each day. The formula that I must use is Daily Budget = Sales/SUM(Sales); * Budget. The sum of the sales should be the sum of all of the sales for the coresponding month.
How can i calculate this in Power BI, I am having a hard time writing up a formula with some columns being different everyday and otheres being the same for each day. FYI the Budget value is differentfor each month.
PS is there a more efficient way to do this, please let me know.
igibson,
Create the following columns in your table.
Month = MONTH(Table[Date])Sumsales = CALCULATE(SUM(Table[Sales]),ALLEXCEPT(Table,Table[Month]))
Daily budget = Table[Sales]/Table[Sumsales]*Table[Bugdet]
Regards,
Lydiaigibson,
Just change the formula of Sumsales to the following:
Sumsales = CALCULATE(SUM(Table[Sales]),ALLEXCEPT(Table,Table[Month],Table[Store]))
Regards,
Lydia
4 Replies
igibson,
Create the following columns in your table.
Month = MONTH(Table[Date])Sumsales = CALCULATE(SUM(Table[Sales]),ALLEXCEPT(Table,Table[Month]))
Daily budget = Table[Sales]/Table[Sumsales]*Table[Bugdet]
Regards,
Lydia
Thank you very much. This is a step in the right direction, however in my whole table I have more stores than just ALR. So currently it is summing the sales of all stores of each month. Do you know of a dynamic way for the salessum to only pull the correct values for each store?igibson,
Just change the formula of Sumsales to the following:
Sumsales = CALCULATE(SUM(Table[Sales]),ALLEXCEPT(Table,Table[Month],Table[Store]))
Regards,
Lydia
mimiAdvocate I
How would you use that to create Budget MTD
