cancel
Showing results for
Did you mean:
New Member

## 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!

1 ACCEPTED SOLUTION

Accepted Solutions
Super User II

## Re: Average value not using row count

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

4 REPLIES 4
Super User II

## Re: Average value not using row count

whydo you divide Door by 12?

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

Super User II

## Re: Average value not using row count

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

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.

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]))))

Announcements

#### Announcing the New Spanish Forum

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

#### 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

Watch Microsoft Business Applications Summit sessions on-demand.

Top Solution Authors
Top Kudoed Authors