Forum Discussion
Calculate All : Include non shown filter
Brianoreilly Please try this as a "New Measure"
Test77 = CALCULATE(SUM(Test77Measure[BillableDays]),FILTER(ALL(Test77Measure),Test77Measure[ProjectID]=SELECTEDVALUE(Test77Measure[ProjectID])))
Test Data :
Output:
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:
