Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
Solved! Go to Solution.
@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,
Just change the formula of Sumsales to the following:
Sumsales = CALCULATE(SUM(Table[Sales]),ALLEXCEPT(Table,Table[Month],Table[Store]))
Regards,
Lydia
@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
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
User | Count |
---|---|
126 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
136 | |
113 | |
102 | |
71 | |
60 |