cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Stein1998
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 nrLocation 1Location 2Location 3
110 10
21010 
3101010

 

Average cost per hour per person

Personel nr.Average cost per hour
120
222
325

 

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
rbriga
Super User
Super User

My suggestion:

  1. Use the Query Editor to unpivot the Hours per person per location table. the result should be:
    Personal nrLocationHours
    1Location 110
    1Location 310
    2Location 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!

View solution in original post

1 REPLY 1
rbriga
Super User
Super User

My suggestion:

  1. Use the Query Editor to unpivot the Hours per person per location table. the result should be:
    Personal nrLocationHours
    1Location 110
    1Location 310
    2Location 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!

Helpful resources

Announcements
Microsoft Build 768x460.png

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_carousel_with_text (1).png

Charticulator Design Challenge

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

May UG Leader Call Carousel 768x460.png

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.

Power BI Release May 2022 768x460.png

Check it out!

Click here to read more about the May 2022 updates!

Top Solution Authors
Top Kudoed Authors