Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
104 | |
101 | |
79 | |
72 | |
64 |
User | Count |
---|---|
142 | |
108 | |
101 | |
81 | |
74 |