Pivot calculated against non pivot data

[Tally04]

Hi there


I need to show how many "builders" that are being planned to use from each company pool of people, but I cannot change my data file to show how many people per company are available (this is in a separate table)

I want to add a pivot field to show

A) % of available people being used on each activity from the company
B) % of overall people being used per company on date

really grateful for any help

[Tally04]

I have updated my attached example to show what I am trying to do.

Possibly this is not something you can achieve in pivots?

[rorya]

You could do something like the attached? I used Get and Transform to unpivot your dates table, then linked it with the other table in the data model and then added a few measures to get the calculations you want.

[Tally04]

Thank you!

Having issues replicating what you did...

I have unpivoted the date columns, do you then create Only connections for both? How do I use these connections in pivot tables

[rorya]

Yes, only connections and check the option to add them to the data model. You can then use Power Pivot to create the pivot table. As a side note, it would be better if your dates were actual dates, then they would sort automatically. You can always format them to just show as mmm-yyyy.