Hi all. I'm relatively new to Power Pivot and am having an issue with multiplying by a percentage of Labor hours that was calculated in a measure.
What I did was create a Total using Total HRS= SUM(Detail([Total Quantity]) where Detail is the table name.
Then I created an All Total Quantity using CALCULATE([Total HRS] , ALL(Detail)
Then I DIVIDE([Total HRS]) , [Total ALL HRS] to get my percentage of hours devoted to particular groups of machinery based on their model number. The percentage I get is the same as the 'Show Values As...' percentage, but that one cant be used in a measure thus compelling me to create one.
When I try to multiply the Total number of Time clock hours by the percentage I've gained above, the result is way off (Low). I don't know if the problem lies with the Time clock hours are coming from a RELATED Kronos table. It's just frustrating. I hope someone out there has a viable answer.
Thanks All
David
Hi David,
I think there are some details missing. Do you have a measure for the 'Total number of Time clock hours'? Is that returning the correct result? If you multiply that measure by your (DIVIDE) Percentage measure with a calculator do you get the same result as your measure?
Are these time clock hours in decimal format or time serial number format? If time serial number format then you need to convert them to decimal time first.
Mynda
Hi Mynda!!
Hours are correct, and everything is in decimal format. Here is a snippet from the table. I hope its enough info. I'm trying to multiply the Kronos column by the Percent Hours. The mach. hours was broken out by Power Pivot, but the Kronos are being brought in from another Related fact table. When I use a calculator, 449.9 * .59 = 265.441. Thanks
| Kronos Hrs. | Mach. Hours | Percent Hours | Kronos/Model |
| 449.9 | 284.502 | 0.59 | 5.387372186 |
| 449.9 | 141.167 | 0.29 | 2.673159308 |
| 449.9 | 0.00 | ||
| 449.9 | 24 | 0.05 | 0.454467569 |
| 449.9 | 16 | 0.03 | 0.30297838 |
| 449.9 | 16 | 0.03 | 0.30297838 |
Hi Mynda!! I solved it!
I was using this as my denominator :=SUM(MES_Hours[MES Hours]) / Calculate([Sum of MES Hours] , ALLEXCEPT(MES_Hours , MES_Hours[Model]))
I changed it to this: =SUM(MES_Hours[MES Hours]) / Calculate([Sum of MES Hours] , ALLSELECTED(DIMModel[Model])) getting the model No. from my DIM table instead of from the fact table. So far so good. Love your training by the way. Where can I get a shirt?
David
Well done, David! It's always more rewarding to solve it yourself 🙂
The I Simply Excel t-shirts are no longer available 🙁 You can usually get Power BI shirts, although it looks like they're out of stock too:
https://www.microsoftmerchandise.com/Shop#/catalog/402-26-T-Shirts
Mynda
