Forum Discussion

igibson's avatar
igibson
Frequent Visitor
8 years ago
Solved

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 al...
Need Help
  • Anonymous's avatar
    Anonymous
    8 years ago

    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

  • Anonymous's avatar
    Anonymous
    8 years ago

    igibson,

    Just change the formula of  Sumsales to the following:

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

    Regards,
    Lydia

Anonymous's avatar
Anonymous
Not applicable
8 years ago

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

igibson's avatar
igibson
Frequent Visitor
8 years ago


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?

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    igibson,

    Just change the formula of  Sumsales to the following:

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

    Regards,
    Lydia