Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Solved! Go to Solution.
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
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
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]))))
@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.
whydo you divide Door by 12?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
43 | |
23 | |
21 | |
15 | |
15 |
User | Count |
---|---|
45 | |
31 | |
30 | |
18 | |
17 |