Dax Calculations/Filtering issues with granularity levels

DAX DAX Calculations
1

Hi everyone,
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)

Thanks,
Shane

2

Hi Shane.

I think you’ve identified the issue while describing it: when slicing the main table to show only “Analysis Account 621” you have filtered-out all other accounts as well. Your [PD Hours G&A] measure and the corresponding [G&A Allocate] measure are thus going to blank. I think the cause of your problems is the data model; I was not able to come up with a “clean” model, but would think that further simplification would help.

Also, your [Date] table should be marked as such.

So, I think you may be able to make some progress on your issue by either:

  1. always selecting the 666 and 667 accounts in the [Analysis Account Name] slicer when you select another account, or
  2. change the FILTER statement in your [PH Hours G&A] measure to get the correct hours even when 666 and 667 are not selected

Hope this helps.
Greg

3

Hi @Shane1,

Welcome to the Forum!

@Greg is correct. When you calculate a value with a fixed Analysis Account, like you did and then overrule that with a slicer selection, the result will be a BLANK…

So I decided to cheat. :wink:
I copied your [PD Hours G&A] measure, removed external context and re-introduced it again.

PD Hours G&A v2 = 
CALCULATE( [Production Direct Hours],
    FILTER( ALL( 'Employee Master Details'),
        'Employee Master Details'[Employee Cost Center] = SELECTEDVALUE( CostCenter[Cost Center Code] ) &&
        'Employee Master Details'[Employee Analysis Account] IN { "666" }))

With this result, when filtered on account 621.

image
image1363×116 10.4 KB

Now I figure you might have to repeat this process for other measures as well to get the desired results. There’s a lot going on and I didn’t work through it all but this looks promising…

I hope this is helpful.

4

Greg/Melissa,

Thank you very much for the help and indeed this will be useful. For now I figured out a workaround by summarizing and crossjoining tables which will take the employee out of anaylysis account 666 and allocate their hours correctly. Unfortunately my slicers now have to come from the newly created table but it does work. I am going back to this report based on your suggestions and seeing if I can get it done correctly through DAX since I feel somewhat cheap with the workaround I have now :slight_smile:.

Melissa and Greg, you both seem to understand my issue since the employees underlying analysis account based on our employee master is 666. And when I filter to the analysis account level on any account other than 666, their hours disappear. Which is why, for lack of better understanding of Power BI to this point, I need to almost virtually switch their analysis accounts based on the allocations. One employee in some of our more complex cost centers could allocate to 6 or 7 different analysis accounts based on the revenue generated for the cost center. This has proven troublesome for my level of expertise to say the least.

Again, I appreciate the help and any further suggestions will be much appreciated.

5

Hi @Shane1,

The key is always the Model…

My personal view is to stick with star schema when at all possible. It makes for a simpler data model, and makes DAX easier to write and debug. Designing a good model can require some thought.

Fact tables should be long and thin whereas Dimension tables short and wider. In Power Query you can reshape and transform your data, making sure all keys for aggregation are present in the right place.

6

hi @Shane1, did the response provided by the contributors help you solve your query? If not, how far did you get, and what kind of help you need further? If yes, kindly mark the thread as solved. Thanks!

7

Unfortunately, I still haven’t figured out how to virtually change the underlying analysis account of an employee when I need to allocate their hours to multiple other analysis accounts. As I said though, i have a workaround so the issue is solved I guess.