The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event! Join us in Stockholm, Sweden from September 24-27, 2024.
2-for-1 sale on June 20 only!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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 |
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
58 | |
34 | |
20 | |
19 | |
15 |
User | Count |
---|---|
44 | |
39 | |
33 | |
23 | |
23 |