Forum Discussion
True Allocated Average
Trying to return the Allocated Cost Per OrderID column because the value in the "Cost" column is the total final cost for the Tracking Number. Essentially you divide the Cost value by however many unique orderID's there are per tracking number. Seems simple but I cant figure it out.
| Tracking Number | OrderID | Cost | Allocated Cost Per OrderID |
| 12345678909 | 1001 | 30.5 | 15.25 |
| 12345678909 | 1002 | 30.5 | 15.25 |
| 987654324 | 1003 | 80 | 20 |
| 987654324 | 1004 | 80 | 20 |
| 987654324 | 1005 | 80 | 20 |
| 987654324 | 1006 | 80 | 20 |
| 2.34873E+12 | 1007 | 18 | 18 |
awitt you can add a column using following expression:
Allocated Cost per order id = VAR __numberofOrders = CALCULATE ( COUNTROWS ( YourTable ), ALLEXCEPT ( YourTable, YourTable[TrackingNumber] ) ) RETURN DIVIDE ( YourTable[Cost], __numberofOrders )✨ Follow us on LinkedIn and to our YouTube channel
I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make effort to give Kudos to whoever helped to solve your problem. It is a token of appreciation!
⚡ Visit us at https://perytus.com, your one-stop shop for Power BI-related projects/training/consultancy.
1 Reply
awitt you can add a column using following expression:
Allocated Cost per order id = VAR __numberofOrders = CALCULATE ( COUNTROWS ( YourTable ), ALLEXCEPT ( YourTable, YourTable[TrackingNumber] ) ) RETURN DIVIDE ( YourTable[Cost], __numberofOrders )✨ Follow us on LinkedIn and to our YouTube channel
I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make effort to give Kudos to whoever helped to solve your problem. It is a token of appreciation!
⚡ Visit us at https://perytus.com, your one-stop shop for Power BI-related projects/training/consultancy.