cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

New Member

## Power BI DAX Expansion on Matrix

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)

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])
)
)

1 ACCEPTED SOLUTION
Community Support

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[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.

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.

Community Support

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[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.

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.

Announcements

#### Fabric certifications survey

Certification feedback opportunity for the community.

#### Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

#### Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors