Forum Discussion
Average value not using row count
Hi,
I have a table structure like this:
Table: Order
| OrderId | Quantity |
| 1 | 10 |
| 2 | 2 |
Table: OrderItem
| OrderId | ItemId | Value |
| 1 | 1 | 15 |
| 1 | 2 | 6 |
| 2 | 1 | 12 |
Table: Item
| ItemId | Name |
| 1 | Door |
| 2 | Window |
Now I want to have a visual to show the average sold value of the items. But it should take the quantity into account:
| Item | Average value |
| Door | (15*10 + 12*2) / 12 = 14.5 |
| Window | (6*10) / 10 = 6 |
How can I create the "Average value" column?
Thx!
as I could imagine you need smth like
Column = DIVIDE(calculate(SUMX('Table (2)', 'Table (2)'[Value]*RELATED('Table'[Quantity]))), calculate(SUMX('Table (2)', 'Table (2)'[Value])))but you need check columns depending on your business logic
4 Replies
whydo you divide Door by 12?
as I could imagine you need smth like
Column = DIVIDE(calculate(SUMX('Table (2)', 'Table (2)'[Value]*RELATED('Table'[Quantity]))), calculate(SUMX('Table (2)', 'Table (2)'[Value])))but you need check columns depending on your business logic
az38, do you need calculate around the SUMX expressions? If so, why?
Robert-bpd, az38:s solutions looks good, but of course it requires proper releations between the tables.
Thx! az38 and Anonymous
I believe this would be the correct version:
Test = DIVIDE(CALCULATE(SUMX(OrderItem; OrderItem[Value] * RELATED(Order[Quantity]))); CALCULATE(SUMX(OrderItem; RELATED( Order[Quantity]))))
