cancel
Showing results for
Did you mean:
Frequent Visitor

## 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.

2 ACCEPTED SOLUTIONS Microsoft

@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

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Microsoft

@igibson,

Just change the formula of  Sumsales to the following:

Sumsales = CALCULATE(SUM(Table[Sales]),ALLEXCEPT(Table,Table[Month],Table[Store]))

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
4 REPLIES 4 Microsoft

@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

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Helper I

How would you use that to create Budget MTD

Frequent Visitor

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? Microsoft

@igibson,

Just change the formula of  Sumsales to the following:

Sumsales = CALCULATE(SUM(Table[Sales]),ALLEXCEPT(Table,Table[Month],Table[Store]))

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.  