cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Frequent Visitor

Calculating daily retail budgets

 

 

Capture.PNG

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
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]

1.JPG

 




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.

View solution in original post

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

View solution in original post

4 REPLIES 4
Microsoft
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]

1.JPG

 




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.

View solution in original post

How would you use that to create Budget MTD


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

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.

View solution in original post

Helpful resources

Announcements
secondImage

Happy New Year from Power BI

This is a must watch for a message from Power BI!

December Update

Check it Out!

Click here to read more about the December 2020 Updates!

Community Blog

Check it Out!

Click here to read the latest blog and learn more about contributing to the Power BI blog!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors