Calculate All : Include non shown filter

[Brianoreilly]

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. 

[Greg_Deckler]

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.

[Brianoreilly]

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. 

[Brianoreilly]

@Greg_Deckler

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

[Greg_Deckler]

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.

[Brianoreilly]

Hi @PattemManohar & @Greg_Deckler,

The solution using the "Selected Value" worked!

Thanks for the help guys 🙂

You are gentlemen. 

[ssugar]

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

[Brianoreilly]

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

[PattemManohar · Accepted 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: