Dynamic Weighted Average by Hierarchy

[Muffin]

Hey everyone, I'm a newer user, who's been doing lots of training, and have learned a lot, but am currently struggling with an employee count calculation I'm working on right now.  I think I've seen posts that I can't seem to find that basically amount to a nested if statement that performance various calculations depending on the circumstance or hierarchy.  However, I'm completely open to any solution that best solves this.

So the goal of this dashboard is to provide accurate PEPM (Per Employee Per Month) based on various products and subscriptions.  Usage can vary by the employee within the organization however, below are a few issues I'm encountering (sorry if this is too much detail):

• I think I need different calculations to be performed based on what hierarchy I'm calculating it at.  In the excel file and screen shot, I've outlined how I think it needs to be performed.
• One of the biggest issues I've found is that I need to aggregate customer/account level data.  ie: in the below for HCM/Time Data (Hierarchy 2), it should be the 4000 (customer 1) + 2300 (customer 2).
  • However, to calculate the employee count, it's important to get a weighted average of the products based on their employee usage, however, it's not straight forward.
    • Simple example: Add-on - Customer 1 - HCM: there are who subscription fees, in one, there are 3000 employees, they have HCM and HCM Products 1, 4, 9, and 12.  There are 1000 Employees with HCM & HCM Products (1, 4, and 9); therefore taking a weighted average of Products 1, 4, and 9 with 4000 employees plus 3000 employees with Product 12, should give 3785 (formula in the excel file for reference).
    • More complicated: For HCM and Time specifically (HCM Solution Keys 100 - 110, and Time 300 - 310), it should sum the below subscription fees.  For HCM/Time - Customer 1 - HCM:  there are two solution key 100's, of 3000 and 1000 (aka: This describes the previously mentioned usage variances in the previous example, there are two populations, their products are the same except Product 12, so there are 4000 total HCM/solution key 100.  Similarly for HCM/Time - Customer 1 - Time: There are 4000 total employees that use Time, 3,478 are hourly, and 522 are salaried.
      • One final wrinkle that I don't think matters, but I'm including in case it does, within Time in this case (it isn't always time, but it is either HCM or Time - never one of the products), there were actually 4600 total time employees, as part of my data cleansing before it gets into BI, these populations are split into a separate Time - Additional solution key.  This is to prevent the dilution of the PEPM calculation when aggregating at the HCM/Time level.  These additional fields are ALWAYS filtered out of the visuals in which PEPM's are being calculated.

Please let me know if I can answer any questions to help get this solution, or attach anything else.  In the excel screenshot and file, I've highlighted in Green where I'm performing the correct calculation in BI, and red where it's not calculating correctly.  

Thanks!

Google Doc - Power BI File 

Google Doc - Math in Excel 

[Muffin]

Hello again,

I've been tinkering with the previous formula, and I was able to solve for that previous post (hierarchy 3) by creating a table within BI that sums the solution to a single number to prevent the weighted average from diluting.  I now have a new table (which I'll attach as a reference in the update power bi file).

I am now trying to solve for the final three levels of my matrix, and was hoping to get some assistance.  Here are screenshots of my BI dashboard, the current DAX measure, and what it should look like in excel with an explanation of the function I'm attempting to perform at each level.  In the power BI screen shot, I left my old columns as a reference to my previous posts, but the only three columns that would be final would be the EE Calc2, the TTL BU SUM ARR, and the BU PM SUM PEPM2.

As stated in my excel, for Account level, I'm attempting to do a weighted average of my hierachy 3 calculation (the next hierarchy down.  So Customer 1 should be a weighted average of 4000 HCM and 4000 time (note that in Time, there are two pieces an Hourly and a Salaried piece - which is why I've been currently struggling to get the calculation to work).  After Account, is Hierarchy 2, which is HCM/Time & Add-On - that needs to be a SUM of the customer/account level data.  So Customer 1 will be 4000 + Customer 5 of 2300, totaling 6200.  Lastly, the EE Band will be a weighted average of Hierarchy 2, so a weighted average based on ARR of 6200 (HCM/Time) and 6138 (add-on).  

Even if someone could point me in the direction of formulas/functions I could use to help tackle these calculations I'd appreciate it - I'm very happy to do most of the leg work, but I'm struggling to find other relevant examples to draw from to help solve these calculations.

BI file as a reference:

Power BI File - Google Drive 

[v-rzhou-msft]

Hi @Muffin ,

Do you want to calculate by different logics in different hierarchy in Power BI ?Here I suggest you to try 

ISINSCOPE() function to achieve your goal. If this reply still couldn't help you solve your issue, please show me a screenshot with the result you want. This will make me easier to find the solution.

Best Regards,
Rico Zhou

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

[Muffin]

ISINSCOPE() definitely helps, I can use it to fix the lowest level (the field name/Product).  I was having trouble calculating the next level up, Hierarchy 3.  I've attached a screenshot of the current calculation and issue (FYI, I attached screenshots of my desired calculation and excel and power bi files in the original post if it helps).  Is there a way to get a weighted average where essentally, you aggregate at the Product (Field Name/Solution Key) level before iterating through the Employee Count * Revenue?  I attempted to do this through an allselected - knowing this wouldn't be the correct math, since I was assuming it would overlay 15,000 for all EE counts instead of the sum at the product level, but that still didn't calculate as I thought it would.  There is also a screenshot from my excel of how I'd ideally like it to calculate to get a value of 3,785.

From Excel: