Forum Discussion
Divide two columns throughout hierarchy
This seems like the most basic thing, but I keep running into it and am fairly new to DAX and Power BI.
I have the following structure. The hierarchy has Toy > Shape > Color. I'm trying to calculate the Unit Cost column, whether I drill down to the shape or color or whatever level. This shouldnt be difficult and have tried "All Selected" but maybe I'm not using it correctly. Help is appreciated, and thanks for going easy on this NewB.
| Category | Description | Qty | Cost | Unit Cost |
| Toy | Blocks | 15 | 224 | 14.9 |
| Shape | Rectangle | 10 | 180 | 18.0 |
| Color | Red | 6 | 20 | 3.3 |
| Color | Green | 4 | 15 | 3.8 |
| Shape | Triangle | 5 | 44 | 8.8 |
| Color | Purple | 2 | 10 | 5.0 |
| Color | Green | 3 | 8 | 2.7 |
Hi,
Assuming Cost and Quantity are measures, try this measure
Unit Cost = [Cost]/[Qty]
Hope this helps.
3 Replies
I guess you are trying to calculate avg cost? If that is true, first you can try creating a column in your table called "Total Cost", this would have to be Qty * Cost. Then your Unit Cost would be a calculated measure: Unit Cost = sum("Total Cost")/sum(Qty)
Hi,
Assuming Cost and Quantity are measures, try this measure
Unit Cost = [Cost]/[Qty]
Hope this helps.
Hi alfordtp ,
Have you solved your problem?
If you have solved, please always accept the replies making sense as solution to your question so that people who may have the same question can get the solution directly.
If you still need help, please share your desired output so that we could help further on it.
Best Regards,
Cherry
