Using an 'Excel-like-array' to calculate hierarchy measure

[Muffin]

Hi all,

I've been tinkering with a hierarchy 'ISINSCOPE()' dax measure that will calculate the correct Employee count based on the hierarchy level in my matrix.  From lowest to highest, it goes 'Field Name' (which is the product sold to a customer) > Hierarchy 3 (which designates if something is HCM or Time) > Account (which is a customer level) > Hierarchy 2 (designating HCM/Time vs Add-on) > and lastly the Employee band of the accounts (which summarizes all customers within a range of employee size).  Below is a screenshot of the current matrix.

I'm currently trying to solve for Hierarchy 2, which is supposed to SUM the customer level data below it.  Because the sum of the customer data is either a sum or a weighted average (depending on whether it's in the HCM/Time or add-on Hierarchy 2), I'm struggling to find a calculation that aggregates at that level without performing a weighted average across all products.  Essentially what I'm trying to do is create a table specifically for this one calculation (I think about this like an array I would use in a VBA calc).  I attempted to create a table, and was able to calculate all the correct values, however, because I summarized the table by a higher level than the my primary key (solution key links to the soltuion lookup table), when I try to reference that table, it can't connect to my matrix.  Is there anyway to replicate a table like this within my Dax calculation so I can summarize by customer/hierarchy 2, and sum the intersection of customer/hierarchy 2 within the matrix?

Below is my current matrix, the table I created to attempt to replicate the array I want, the Dax associated with that table, and the dax associated with the current ISINSCOPE() function, and lastly an excel sheet replicating what I'm attempting to solve for.

I'm more than happy to attach/send any other information that might help solve this.  Also any topics that might lead me down the right path are also fine.  Thanks in advance.

Cheers

[lbendlin]

You already use SUMMARIZE.  Now take the next step and use it in a measure. You can operate on table variables  (call them arrays if you like) in your measures as long as the final result of the measure is a scalar value.

[Muffin]

Hi, so I am be misinterpretting, so feel free to correct me if I am, but I believe I already tried to create a measure and incorporate into my matrix, but what I think the issue is, is that I can't connect to the Solution Hierarchy table because the summarize function removes that info to connect back to that table.  Below is what happens when I put that measure in the ISINSCOPE() function for Hierarchy 2 (HCM/Time & Addon) - I believe it's summing the entire table because it can't reference back to my solution lookup table.

Below are a few screenshots of the matrix, the current dax, the measure I created for that table's EE's, and my current table relationships (values in the middle, reference tables on the outside).

Let me know if there's anything else you think I could do, happy to try!

 

[lbendlin]

Please provide sample data that covers your issue or question completely.
https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-...
Please show the expected outcome based on the sample data you provided.

[Muffin]

Really appreciate your help.  Here are two files, the Power bi file, and an excel sheet replicating that matrix with the math I'm attempting to replicate (also screenshot below).  I'm not married to any code I've already written - I'm fairly new to BI - definitely still learning.

Thanks again, happy new year!

Google Drive - Test File Power bi 

Google Drive - Excel Sheet 

[lbendlin]

Thank you for providing the sample data. It's a little too much, so will take some time to clean up. 

Note that DAX has a SWITCH command that you can use instead of nested IFs

Quarter = SWITCH(TRUE(),'Quote Info'[Book Month] IN {"Oct","Nov","Dec"},"Q1", 'Quote Info'[Book Month] IN {"Jan","Feb","Mar"},"Q2",'Quote Info'[Book Month] IN {"Apr","May","Jun"},"Q3","Q4")

Attached is a basic framework  - please indicate which datapoints you want to focus on as the sample data doesn't seem to match the pictures you posted (specifically for EE Band)

[Muffin]

Appreciate the Switch idea - it's a new function that I was just learning about as I was attempting to get this EE calculation to work.

Question - in the file that you sent back - you removed all my previous work? (totally fine, just wanted to make sure I'm opening it correctly.
The only help I really need is getting the EE's to calculate correctly at each level of the hierarchy.  I was focusing on just the EE band of 1500 - 5000 because Customer 1 & 5 are good examples of the complexity within the calculations.

Also here is an excel that I believe outlines what I'm trying to accomplish in the EE count column of each hierarchy.  Please let me know if this helps - I really appreciate your help, and don't want to assume this answers all your questions!

[lbendlin]

I removed all the measures to distill the problem down to its essence.  As I mentioned earlier you provided a little too much information which distracts from the task at hand a bit.  (Don't get me wrong, this is definitely better than not having enough details to work with)

I can't make your sample data match the expected list.  Where does "Time (Hourly) - Additional" get filtered out?

see attached

[Muffin]

Ahh, sorry about that.  So the additional EE's are filtered out in two ways (most likely because I'm probably not doing it the most optimally).  The additional EE's dilute the PEPM (per employee per month charge) calculation, so whenever there is a PEPM in a visual (which there is in this case), they should ALWAYS be excluded. 

I usually do this by first removing it through the filter on the page/visual (below), but also in calculations where I'm applying filters through the calculation (since I believe the filter in a calculation will override the filter context from the visual - but I'm never 100% there since I'm pretty new to BI).  Below is the filter from the visual ex, the hierarchy with the Hierarchy 4 being the base/additional (you can exclude additional to remove these), and a sample from my previous calculation on how I was removing it with a filter.

Does that answer that question?

[lbendlin · Accepted solution]

I think a report or page level filter is more flexible.  Have a look a the version that is attached, it should give you what you need for the "EE Count" column.

[Muffin]

This is amazing, just one issue, that I can probably figure out on my own.  In Hierarchy 3, in the HCM/Time piece, it appears to be doubling the value (screenshot below).  What I thought might be happening, is since it looks to be a Max with an or, I assumed that the <=310 might be capturing both the HCM & Time, but I assume that the && solution[hierarchy 3]="Time" should be controlling for that.  If you think there's an easy fix, let me know, otherwise, I'll play around with it.

Really appreciate your help - I very much look forward to reading through all your Dax and learning a lot!

[lbendlin]

Download the file again, I corrected that measure.