cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Robert-bpd
New Member

Average value not using row count

Hi,

I have a table structure like this:

Table: Order

OrderIdQuantity
110
22

 

Table: OrderItem

OrderIdItemIdValue
1115
126
2112

 

Table: Item

ItemIdName
1Door
2Window

 

Now I want to have a visual to show the average sold value of the items. But it should take the quantity into account:

ItemAverage value
Door(15*10 + 12*2) / 12 = 14.5
Window(6*10) / 10 = 6

 

How can  I create the "Average value" column?

 

Thx!

1 ACCEPTED SOLUTION

Accepted Solutions
Super User II
Super User II

Re: Average value not using row count

@Robert-bpd 

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


do not hesitate to give a kudo to useful posts and mark solutions as solution
LinkedIn

View solution in original post

4 REPLIES 4
Super User II
Super User II

Re: Average value not using row count

@Robert-bpd 

whydo you divide Door by 12?


do not hesitate to give a kudo to useful posts and mark solutions as solution
LinkedIn

Super User II
Super User II

Re: Average value not using row count

@Robert-bpd 

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


do not hesitate to give a kudo to useful posts and mark solutions as solution
LinkedIn

View solution in original post

UlfBergqvist Responsive Resident
Responsive Resident

Re: Average value not using row count

@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.

Robert-bpd
New Member

Re: Average value not using row count

Thx! @az38 and @UlfBergqvist 

 

I believe this would be the correct version:

Test = DIVIDE(CALCULATE(SUMX(OrderItem; OrderItem[Value] * RELATED(Order[Quantity]))); CALCULATE(SUMX(OrderItem; RELATED( Order[Quantity]))))

 

 

Helpful resources

Announcements
Announcing the New Spanish Forum

Announcing the New Spanish Forum

Do you need help in Spanish? Check out our new Spanish community section.

April 2020 Community Highlights

April 2020 Community Highlights

Info on our Super Users, MBAS content and badges, and updates to our support articles. - Read the full Community Highlights.

MBAS Gallery 2020

MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.