I am very new to Power BI and this is my first post on the forum so please be gentle. If I am somehow not following proper protocols, please let me know as well. I am really struggling with a report in which I am trying to calculate hourly burden rates through a couple of layers of granularity (we simply divide total expenses by the total hours of our direct employees). I normally am able to reference the endless amounts of content on the website and figure out my issue but this has had me stumped for quite some time. The calculation is not necessarily what I am struggling with but rather trying to get the calculation down to the final layer of granularity.
At my company (without going into too much detail) we have a basic hierarchy of cost centers, revenue streams, and analysis accounts. Mostly the analysis accounts match up to the revenue streams so we can correctly match the expenses to the revenue generated. We do however have a couple of allocation analysis accounts that will allocate across different revenues to account for those people and expenses that generate more than one type of revenue.
Our ERP has a nice algorithm that does this for us from an amount standpoint, but I need to now replicate the same allocation with employee hours since I am working on our burden rates for specific revenue streams.
The two analysis accounts that allocate are 666 (G&A – allocates to all other analysis accounts within a cost center based on revenue) and 667 (Welding G&A – allocates only to the welding analysis accounts based on revenue which are 618,619,620,633,&632)
Hence my problem. If you look at my report, you will see a simple table where I have put my measures to check the math. The first measure I calculated is “Production Direct Hours” which is a sum of the total hours filtered by employees who are considered direct vs indirect. Direct employees are “FALSE” in the employee master. Next, I took all the hours associated with the 2 allocation analysis accounts (666 & 667) out with the measure “PD Hours”. Next, I took the % of revenue for the entire cost center and use that to allocate all hours in G&A (666) with the measure “G&A Allocate”. I can tell those hours by the analysis account the employee is coded to in our employee master. Lastly, I took all hours in Welding G&A (667) and allocate those to only the welding revenue analysis accounts mentioned above with the measure “Welding Allocate” (these revenue streams are only in our two cost centers; 3 and 8).
Everything works fine so far. I next add up the hours with “TPD Hours” by summing PD Hours, G&A Allocate, and Welding Allocate. This is the last column of the table and exactly how I want the hours to calculate when using them for the burden rate calculation. The hours should allocate from 666 and 667 to revenue generating analysis accounts.
Unfortunately, when I try and filter down to the analysis account level, the allocated hours do not stay in the TPD Hours measure calculation. I hope an expert has the time to look at this and ends up telling me there is an easy fix because as of now I have not found it.
If you look at the table when opening the file, see the first line which is cost center “Cost Center 2” and “Analysis Account 621”. The direct hours associated are 711.6 and then I calculate another 439.05 needs to be allocated from G&A based on the revenue in 621. The TPD hours is correct at 1150.65 and is the number of hours I need to use in the burden rate calculation. When filtering to “Analysis Account 621” in the third slicer, you will see the 439.05 comes out of the TPD Hours and it reverts back to just the PD Hours total.
I know this is long winded, but I would really appreciate some insight if possible. If an expert has any time to look at the file and has any questions, please feel free to ask. Hopefully you will be able to see the pbix file I uploaded with my attempt at somewhat anonymizing the data.
BR Forum.pbix (977.3 KB)