topic Re: Distributing Hours to the facilities from a Facility. in DAX Commands and Tips

Distributing Hours to the facilities from a Facility.

Rabi — Tue, 30 Jan 2024 23:36:18 GMT

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

3500 is the resulting value from the first measure.

parry2k Greg_Deckler lbendlin

Re: Distributing Hours to the facilities from a Facility.

parry2k — Wed, 31 Jan 2024 00:28:32 GMT

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!

Re: Distributing Hours to the facilities from a Facility.

Rabi — Wed, 31 Jan 2024 00:43:46 GMT

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

Re: Distributing Hours to the facilities from a Facility.

parry2k — Wed, 31 Jan 2024 02:19:36 GMT

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

Re: Distributing Hours to the facilities from a Facility.

parry2k — Wed, 31 Jan 2024 02:27:18 GMT

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.

Re: Distributing Hours to the facilities from a Facility.

Rabi — Wed, 31 Jan 2024 03:40:43 GMT

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 !!