Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Reply
pbisupport1717
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)
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

Screenshot 2024-04-01 102108.png (Showing correctly)

Screenshot 2024-04-01 102124.png

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

 

Screenshot 2024-04-01 102143.png

(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!


1 ACCEPTED SOLUTION
v-yohua-msft
Community Support
Community Support

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.

View solution in original post

1 REPLY 1
v-yohua-msft
Community Support
Community Support

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.

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

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