Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi Folks,
I have a Timecard Table with Resource Names, Projects Names and Billable Days Worked.
In the example below, I have a subset of two projects, that looks at the time worked from a "Resource Perspective" and a "Project Perspective".
The total billable days comes to 2,721.41 days.
In the measure "Days Logged Billable All Project Dimension", I want to show the Project Granularity Value beside the Resource Value, but it is showing the overall total. I need this to relate back to the project worked on.
For instance the "Resource : Alan" only worked on the first project in the project table, so he should show:
Resource Name: Days Logged Billable All Days Logged Billable All Project Dimension
Alan 23.74 1,502.74
How can I get this measure to relate to the project worked on by resource?
Please note that this needs to be done via a measure and cannot be hard coded or worked via "if" hard code.
Appreciate the help.
Solved! Go to Solution.
@Brianoreilly Please try this as a "New Measure"
Test77 = CALCULATE(SUM(Test77Measure[BillableDays]),FILTER(ALL(Test77Measure),Test77Measure[ProjectID]=SELECTEDVALUE(Test77Measure[ProjectID])))
Test Data :
Output:
Proud to be a PBI Community Champion
Sample data would help tremendously. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
That being said, the problem is likely your ALL filter so perhaps ALLEXCEPT([Resource Name) but really tough to tell what exactly is going on.
Thanks @Greg_Deckler
The image posted is essentially the sample data.
I have one table.
Timecard Table
For example:
Resource Name | Project ID | Billable Days.
Alan 1 10
John 1 30
Amy 2 50
I want a calculated measure to show the sum of the total project billable days for all projects that the person has worked on.
Result:
Resource Name | Days Logged Billable All| Days Logged Billable All Project Dimension
Alan 10 40
John 30 40
Amy 50 50
For instance Alan worked on Project 1 for 10 days. John also worked on Project 1 for 30 days, so the Total Project Billable Days should be 40.
Hope this helps.
I basically want the sum off all the Billable Project Days that the Resource has worked on.
If a resource spent 50 days billable on a project and someone else spent 150 days.
The figure I want is 200
I'm fairly certain that you will need to have a separate table of Project ID's as a dimension table in order to pull this off. Let me see what I can come up with.
Hi @PattemManohar & @Greg_Deckler,
The solution using the "Selected Value" worked!
Thanks for the help guys 🙂
You are gentlemen.
Given your sample data, I was able to do the following:
1. Create a calculated table with the following DAX:
Project Table = SUMMARIZE('Timecard Table', 'Timecard Table'[Project Id], "Billable Days", SUM('Timecard Table'[Billable Days]))
2. Create a relationship between the Project Table and the Timecard Table (autodetect was able to pick up the relationship)
3. Create a table visualization with the following fields:
- Resource Name
- Project Id
- Billable Days (from the Timecard Table)
- Billable Days (from the Project Table)
4. Set the Billable Days (from the Project Table) to not summarize in the table visualization.
Here's the resultant pbix file for your review https://github.com/ssugar/PowerBICommunity/raw/master/community-sol-560918.pbix
Thanks @ssugar,
However this will not work as I want to use a date slicer and I need to calculate this via a measure.
Thanks for trying though.
Regards
@Brianoreilly Please try this as a "New Measure"
Test77 = CALCULATE(SUM(Test77Measure[BillableDays]),FILTER(ALL(Test77Measure),Test77Measure[ProjectID]=SELECTEDVALUE(Test77Measure[ProjectID])))
Test Data :
Output:
Proud to be a PBI Community Champion
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
94 | |
81 | |
66 | |
58 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |