Distributing Hours to the facilities from a Facility.

[Rabi]

Hi Everyone,

Please help me with the following issue:

I used this dax to calculate the Maintenance Hours

Maintenance Hours (Head Office) =
           var Hours= CALCULATE( SUM (Table A [Hours] )
          , Table A [Department] IN { "Maintenance" },
          Table A '[Village Name]="Head Office") 
return 
CALCULATE(Hours,REMOVEFILTERS(Table A[Village Name]))

 Now i need to distribute the resulting hours from above dax to other villages, i used the following dax but its not working as i have specified the Head Office in above dax, how can i make the resuting hours from above dax to act as hard value. I am distributing the hours based on the bed capacity of the villages

Head Office distribution Maintenance = 
VAR totalbed = CALCULATE(SUM(Table B[Bed Capacity]),
 ALLSELECTED(TABLE A[Village Name]))

VAR EachFacilityBeds = SUM(Table B[Bed Capacity])
RETURN
[Maintenance Hours (Head Office)]*EachFacilityBeds)/totalbed

I used the following Measure in a matrix but it doesnt give me the distributed hours, If i use the hard value like below then i get the corrrect distribution

Head Office distribution Maintenance = 
VAR totalbed = CALCULATE(SUM(Table B[Bed Capacity]),
 ALLSELECTED(TABLE A[Village Name]))

VAR EachFacilityBeds = SUM(Table B[Bed Capacity])
RETURN
(3500*bed)/totalbed

3500 is the resulting value from the first measure.

@parry2k  @Greg_Deckler @lbendlin 

[parry2k]

@Rabi also do you want these as columns or measures? If you want columns I would do it in PQ, but if need measures then of course it will be DAX. 

[Rabi]

Hi Parry, 

I would prefer dax over PQ however, i am keen ko learn how would it be done in PQ, the hours can be divided in equal portions.

Thanks Very Much !!

[parry2k]

@Rabi 40 hours from h/o distributed to other facilities in equal portion or there are other strings attached?

[parry2k]

@Rabi very hard to tell what you are trying to achieve. It will be easier if you throw a sample pbix file or sample data with the expected output. Measures are not explaining much, at least to me. Thanks!

[Rabi]

Hi Parry, 

Below is the sample data,

I want to Distribute the 40 hours from Head office to the villages A,B,C,D,E, The result would be 8 hours distributed to each villages. I have used same logic above.

Thanks !!

Facility	Job Title	Worked Hrs	Amount
A	Maintenance	8	100
B	Maintenance	8	100
C	Maintenance	8	100
D	Maintenance	8	100
E	Maintenance	8	100
Head Office	Maintenance	40	500