Forum Discussion
Multiply with averages
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
My suggestion:
- 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
- Connect the now-unpivoted table to the Average cost per hour per person table by Personal nr.
- 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.
- Use the Query Editor to unpivot the Hours per person per location table. the result should be:
1 Reply
My suggestion:
- 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
- Connect the now-unpivoted table to the Average cost per hour per person table by Personal nr.
- 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.
- Use the Query Editor to unpivot the Hours per person per location table. the result should be: