Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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.
@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.
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
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 !!
@Rabi 40 hours from h/o distributed to other facilities in equal portion or there are other strings attached?
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
@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!
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
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 |