Cannot manage to create the proper pivot table

[trsh3r]

Good day all!

I've been tinkering with this one for a while but cannot seem to get the expected result, so thought I'd reach out.

Quick context:
I have a table (below) listing for each team member their assignment(s) and non-billable activities (training, vacation, and whatnot)

 

What I'm trying to do: forecast for each team member, and given periods of time the amount of billable (assignment) days.

How I've been trying to play it so far: 

• for each line, I calculate non-billable days for each period in report (Previous, Current, and next 5 periods), 
• then for each line, I calculate billable days for each period in report

To do so, I must pivot by team member name, so I can first sum up and remove their non-billable per period

eg. for Anne, in second period of August, she's unavailable on the 16th, so she'll only bill 11 days out of 12 working days in the period

This is where I get stumped.

I tried creating a pivot table with the following DAX

PIVOT = 
SUMMARIZECOLUMNS (
    'TRANSFORMED DATA'[Name],
    'TRANSFORMED DATA'[NBD Prev],
    'TRANSFORMED DATA'[NBD P0], 
    'TRANSFORMED DATA'[NBD P1]
)

But the result still shows a line per person per unavailable period

instead of the intended result, which would be

What am I doing wrong? Any easy way to correct this, or should I go at it a whole other way?

Thank you for your help

PS: pbix file here, if needed 

[trsh3r · Accepted solution]

if anyone needs it, I found my own answer: a simple measure calculated with SUMX, created for each period.

SumP0 = 
SUMX (
    VALUES ('TRANSFORMED DATA'[Name]), 
    CALCULATE (
        SUM ('TRANSFORMED DATA'[NBD P0])
    )
)

[foodd]

Please provide your work-in-progress Power BI Desktop file (with sensitive information removed) that covers your issue or question completely in a usable format (not as a screenshot).

https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

This allows members of the Forum to assess the state of the model, report layer, relationships, and any DAX applied.

[trsh3r]

Thank you for your tips. The PowerBI desktop file is included at the end of my post