Office Allocation Count Issue

[sirlanceohlott]

Good morning, 

I have an allocation by office requirement for a report, and with the help of @parry2k I've been able to get to the point where allocation by office works perfectly when placed into a table 🙌 

He provided a measure that worked perfectly in the sample file:

% allocated to office = 
VAR __totalOfficeinThisJob = CALCULATE ( COUNTROWS ( SampleData ), ALLEXCEPT ( SampleData, SampleData[jobOrderID] ) ) 
RETURN
DIVIDE ( 1, __totalOfficeinThisJob )

Unfortunately, the fact table holds multiple lines per JobOrder due to it having SubmissionIDs, Candidate IDs, so I had to make an adjustment to make the measure work, which is below: 

Allocated By Office = 
VAR __totalOfficeinThisJob = CALCULATE ( DISTINCTCOUNT( HI_JobOrder_BI_Fact[Office] ), ALLEXCEPT ( HI_JobOrder_BI_Fact, HI_JobOrder_BI_Fact[jobOrderID]))
RETURN
DIVIDE ( 1, __totalOfficeinThisJob ) 

The above measure allowed for the proper allocation to work due to the conditions of the fact table, so I created the below measure to SUM these new allocations:

Allocation Office Job Count = SUMX(HI_JobOrder_BI_Fact,[Allocated By Office])

The above measure works perfectly in a table where there is only one row per JobOrder (see below)

But... When I utilize that same formula in my actual report the sums are extremely high which has me thinking it is counting everything...

I'm needing another pair of eyes to help me turn the corner. 

I appreciate your help in advance 🙂

[parry2k · Accepted solution]

@sirlanceohlott update this measure as below, didn't had chance to review all the details but I think this will do it.

Allocation Office Job Count = 
SUMX(
VALUES( HI_JobOrder_BI_Fact[jobOrderId]),
[Allocated By Office]
)

[sirlanceohlott]

That did the trick, thank you thank you thank you 🙂

[parry2k]

@sirlanceohlott glad to help. that was quick for you to test. Cheers!!

[sirlanceohlott]

@parry2k , I appreciate it, I'll let you know if the above adjustment makes it all click into place.