Getting an average for end of year to be applied throughout the year

[seanpratt]

Probably a very easy solve here. I have a small table that looks like this:

I basically want whatever the year totals to be for each 001 and 005 to be divided by the grand total... but that value to be populated in each month. So, the final total for 2020 for '001' is 275,375 / 6,424,440 = 4.29%. Even though that's an end of year value, I want it to be valid for Jan 1, Feb 1, etc. There are other numbers that I will be plugging in that will be multipled off this value monthly. 

Does that make sense?

[Ashish_Mathur]

Hi,

Try this measure

=CALCULATE([Total],ALL(Calendar[Date]))/CALCULATE([Total],ALL(Calendar[Date]),ALL(Data[Code]))

I have assumed:

1. The Dates in your image are from the Calendar Table
2. [Total] is a measure (the second column in your image)
3. Code is the field in the Data Table which has 001, 005 etc.

If this does not help, then share the link from where i can download your PBI file.

[seanpratt]

@Ashish_Mathur That worked perfectly! I think I'm just down to the final column now and once again having trouble creating a "static" calculation that ignores the dimension.

The result of my attempt is "test" which is correct in what I wanted to show but I need it to almost ignore the '001' and '005'. I just need it based on totals. So January is 19.14%, March is 20.43%.. etc.

Does that make sense?

[Ashish_Mathur]

Hi,

I do not understand the logic of 19.14 and the other figures in the yellow column.  Share the link from where i can download your Excel workbook and explain the calculation clearly.

[seanpratt]

Dropbox Link 

Attached here. 

Extended Example is our historic way of doing it within Excel.

TableData is the tabular format I'm hoping to enter everything in as because we are putting these budgets into Power BI and we want a DAX formula(s) that allow us to continue adding departments as we expand that this "weighted budget" will adjust with.

The 'Sheet2' tab is the sandbox I'm trying to build out this logic. Basically, you can see that for January 1, 2020 - 001 has 0% results achieved and the overall budgetary weight of this project (001) is 4.29% which equals 0.... For 005 - it is 20% achieved with an overall budget weight of 95.71%... this means that at January 1 our budgeted expense (for results) is 19.14% of our total budget.

Basically I want to add the values across departments by each month so January is (19.14+0), February should be (19.14+0.43), March should be (19.14+1.29) and so on. I've also included my Excel chart I had created that should kind of show you (the grey line). We have budget by calendar and also a budget by results - this is the latter. 

[Ashish_Mathur · Accepted solution]

Hi,

Remove they key results field from the Pivot Table and write these measures

Key result = if(HASONEVALUE(Dates[Date]),SUM('DataTable'[Key Results]),BLANK())

Measure1 = SUMX(ALL('DataTable'[Trial]),[Key result]*[Weight(Year)])*100

Hope this helps.

[seanpratt]

Wow! That's it, alright. Thank you. Just added more of our departments and hit refresh and voila... perfect solution!

[Ashish_Mathur]

You are welcome.  Thank you for your kind words.

[amitchandak]

@seanpratt , not sure I got it , but try

https://community.powerbi.com/t5/Desktop/Percentage-of-subtotal/td-p/95390