Multiply with averages

[Anonymous]

Hi all,

I have the following question.

I have trouble to multiply an average with an absolute number, I think it is because there is no link between the tables posible.

I have the following tables:

Hours per person per location

Personel nr	Location 1	Location 2	Location 3
1	10		10
2	10	10
3	10	10	10

Average cost per hour per person

Personel nr.	Average cost per hour
1	20
2	22
3	25

I try to calculate the cost per location. I thought I could calculate the hours multiplied by the average costs, but it doesn't work.

There is no link from the cost per personel and the the locations.

Can someone help me?

Kind regards,

Stein

[rbriga · Accepted solution]

My suggestion:

1. Use the Query Editor to unpivot the Hours per person per location table. the result should be:

   Personal nr	Location	Hours
   1	Location 1	10
   1	Location 3	10
   2	Location 1	10

2. Connect the now-unpivoted table to the Average cost per hour per person table by Personal nr.
3. Create a Measure:

Cost per Location=

SUMX('Hours per person per location', 

SUM('Hours per person per location'[Hours]*RELATED('Average cost per hour per person'[Average cost per hour])

)

Give it a try.