How to calculate multipla dimensions totals per a single dimension

[#1]

Hello Community, if there are other threads that can answer my question please provide me a link.

What I am trying to archive as per the two tables of Staff and Branch below is find an expression that can calculate the number of colleagues each staff has per the branch they work for.

I would like to archive something similar to the green table in the picture.

[sunny_talwar]

May be this?

Staff:

LOAD * Inline [

StaffID, StaffName, BranchID, SalesQuota

1, John Doe, 1, 1000

2, Sally Tom, 2, 2000

3, Petter Sam, 1, 1000

4, James Ken, 1, 1000

];

Left Join (Staff)

LOAD BranchID,

  Count(StaffID) - 1 as NumberOfColleagues

Resident Staff

Group By BranchID;

[#3]

Staff:

Load * Inline [

StaffID, StaffName, BranchID, SalesQuota

1, John Doe, 1, 1000

2, Sally Tom, 2, 2000

3, Peter Sam, 1, 1000

4, James Ken, 1, 1000

];

left join(Staff)

Branch:

Load * Inline [

BranchID, BranchName

1, B1

2, B2

];

left join(Staff)

Count:

Load BranchID, count(StaffID)-1 as Count

Resident Staff

group by BranchID;

[#4]

Thank you Sunny. Your solution works for me.

However is there a way to archive the same result via set analysis?

[sunny_talwar]

Not sure how this will play out with the real data, but try this:

=Aggr(Count(TOTAL <BranchID> BranchID), BranchID, StaffName) - Count(BranchID)

with StaffName as the dimension