cancel
Showing results for
Did you mean:
New Member

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

1 ACCEPTED SOLUTION
Super User

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.

-------------------------
Data analyst by day, hockey goalie by night.
Did I help? Then please hit that "kudos" or "accept as a solution" button!
Super User

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.

-------------------------
Data analyst by day, hockey goalie by night.
Did I help? Then please hit that "kudos" or "accept as a solution" button!

Announcements

Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!

What difference can a User Group make for you?

At the monthly call, connect with other leaders and find out how community makes your experience even better.