Power BI DAX Expansion on Matrix

[pbisupport1717]

Okay so I am making a power bi report. I have the following rows (all in one table) visualized as matrix. in brackets is what the rows refer to

Within the Entries table
Groups.name (Department)
User_name (Employee)
Tasks.level 1 Name (Parent Project)
Name (tasks)

Now this data is basically showing me time logged by employees across departments in certain tasks grouped by certain parent projects. There are some tasks that are called "Application Development Phase". These tasks are approved for Capex treatment on my budget. I need to track these specific tasks as a percentage of overall hours logged by each user.

Here's what I've done so far.

1) I've created some measures

User Total Hours = SUM(Entries[Hours])
App Dev Hours = CALCULATE(SUM(Entries[Hours]),Entries[name]="application development phase")

I now am stuck while creating a Capitalizable Hours %.
Ideally, the formula would be
Cap Hours% = DIVIDE([App Dev Hours],[User Total Hours])

However here is the problem

 (Showing correctly)

(Expanding to parent project showing incorrectly, presumably, denominator is changing)

(Showing incorrectly)

1) This formula shows correct calculations when my matrix is expanded till the user name level. It calculates with the denominator of total hours logged by the user (see first image)

2) But when I expand to the Tasks.level 1 Name (Parent Project) or
Name (tasks) levels on the matrix, it looks like the denominator changes. (mage 2 and 3)

Keep in mind, the following
1) this is a large data set of entries. It has entries for multiple months. So I have a slicer to right now show me march
2) Under each employee there might be multiple parent projects
3) under these parent projects, there might be multiple tasks, many of which could be "Application Development Phase", some might not. I only want to show this calculation where relevant
4) and i want the denominator to remain constant -i.e total hours logged by the user, no matter if I expand my matrix. The neumerator (i.e app dev hours) can remain dynamic and currently is performing as I want it to when i expand the table

How do i calculate Cap Hours% considering all this

I've tried a bunch of ALLEXCEPT combinations but doesnt seem to work. The below formula reduced my percentage calculation at the user name level (which was the only one that was working to begin with) 

Cap Hours % =
DIVIDE(
CALCULATE(
[App Dev Hours],
ALLEXCEPT(Entries, Entries[User_name])
),
CALCULATE(
[User Total Hours],
ALLEXCEPT(Entries, Entries[User_name])
)
)


 Please help!

[Anonymous · Accepted solution]

Hi, @pbisupport1717 

We need to make sure that the denominator remains the same at all levels of the matrix, regardless of the drill-down level. You might want to try the following DAX

Cap Hours% = 
DIVIDE(
    [App Dev Hours],
    CALCULATE(
        [User Total Hours],
        REMOVEFILTERS(Entries[Tasks.level 1 Name]),
        REMOVEFILTERS(Entries[Name])
    )
)

In this formula, any filters applied to the column are used to remove when drilling down, ensuring that the calculation is based on the total number of hours logged by the user across all tasks and parent projects.

How to Get Your Question Answered Quickly 

Best Regards

Yongkang Hua

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