Calculate Sum 'Revenue Budget' based on branch location\branch location code for each month

[laehatlav]

Hello, I am a new beginner for power bi. I have been searching for an answer to this calcultion but have not been able to find. I am trying to calculate the sum for a revenue but based on the branch and for each month. For example, i want to get the sum of the revenue budget for branch Mira Loma for the month of January, then for February, and on. Also want to calculate for same for the other branch location. Any suggestion and help would be greatly appreciated. 

[v-xicai]

Hi @laehatlav ,

You may create measure like DAX below.

Total Revenue=CALCULATE (SUM (Table1[Revenue Budget] ),FILTER (ALLSELECTED(Table1), Table1[branch location]= MAX(Table1[branch location]) &&MONTH(Table1[branch location])= MONTH(MAX(Table1[branch location]))))

Best Regards,

Amy

Community Support Team _ Amy

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[laehatlav]

Hi Amy,

here is a screenshot of my spreadsheet i loaded on my desktop power bi.  From the screenshot I have branch code 001 for month Jan, Feb, March, to Dec. I also have other branch code 002, 003, 004, 005 with month Jan, Feb., and on. I would like to sum the total for each month per branch code. I tried seveal measures but i keep getting an error for the total. Thank you for the help.

screenshot

[amitchandak]

Not sure what is needed.

You can use the slicer to filter. Or filter in calculate

calculate(sum(table[column]), table[branch]="ABC", table[month]="January")

[kentyler]

If you have a table with the amount of the budget for different dates, with a field for branch and a field for the date. Then you will need to have a way to know the "month"

You can either add a calendar table (or a "date" table), relate it to your budget table and use the month field from the calendar table

or you can create a simple calculated column that gives you the month value for each date

Then

You can place the fields in a tabular visual on a power bi report and the visual will allow you to automatically group by month and by branch and sum up the amounts for combination using the built in settings of the tabular visual.
If you would like more help on this supply a small sample of the data you are working with and I will put it in a power bi file and build an example.  or upload a power bi file with sample data in it.

I'm a personal Power Bi Trainer I learn something every time I answer a question

The Golden Rules for Power BI

1. Use a Calendar table. A custom Date tables is preferable to using the automatic date/time handling capabilities of Power BI. https://www.youtube.com/watch?v=FxiAYGbCfAQ
2. Build your data model as a Star Schema. Creating a star schema in Power BI is the best practice to improve performance and more importantly, to ensure accurate results! https://www.youtube.com/watch?v=1Kilya6aUQw
3. Use a small set up sample data when developing. When building your measures and calculated columns always use a small amount of sample data so that it will be easier to confirm that you are getting the right numbers.
4. Store all your intermediate calculations in VARs when you’re writing measures. You can return these intermediate VARs instead of your final result  to check on your steps along the way.