Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Productivity- Pallets per man hour

Hi All, 

Fairly new to Power BI, 

Could some one help me with below 

Table 1- where i have Month/year, Customer and Hours 

Table 2- Where i have month-year, Customer and Pallets

 

I need to find productivity, Pallets/hour for each month. 

I tried adding relationship of cardinality many-Many to Month-year (table 1 and table 2)- Added a measure. It did not work

I also tried using lookupvalue at table 2( i was trying to see if i can get hours and pallets inteh same table) and it did not work. I can get values only for the 1 year and not after that. 

 

Any leads will be very much appreciated. 

Thanks, 

V

1 ACCEPTED SOLUTION

@Anonymous 

I created a calendar table linked to both tables, you measure is simple now.

You can download the file: HERE



Measure 5 = 
VAR _Hours= Sum(Table1[Hours])
VAR _Pallets= SUM(Table2[Total Pallets])
RETURN
DIVIDE(_Pallets,_Hours)

Fowmy_0-1601496135977.png

________________________

If my answer was helpful, please consider Accept it as the solution to help the other members find it

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube  LinkedIn

 



Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

View solution in original post

8 REPLIES 8
Fowmy
Super User
Super User

@Anonymous 

Insert a Table Visual on the canvas and drag and drop the Month-Year field from Table1, Add the following measure. No relationship is required.

Measure = 
VAR __Hours = SUM(Table1[Hours])
VAR __Pallets = 
CALCULATE(
    SUM(Table1[Pallets]),
    FILTER(
        Table2,
        Table2[month-year] = MAX(Table1[month-year])
    )
)

RETURN
DIVIDE( __Pallets, __Hours )

 

________________________

If my answer was helpful, please consider Accept it as the solution to help the other members find it

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube  LinkedIn

Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

Anonymous
Not applicable

@Fowmy 

Thanks for the reply, I tried this but i do not get values beyond 12 month. Although i have data upto August 2020.

Do you know Why?

Varsha1189_0-1601490897511.png

 

@Anonymous 

Check if you have set any visual filter or page level filters. Remove the relationship

You can share your sample file to have a look

________________________

If my answer was helpful, please consider Accept it as the solution to help the other members find it

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube  LinkedIn

Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

Anonymous
Not applicable

@Anonymous 

It is password protected

 

________________________

If my answer was helpful, please consider Accept it as the solution to help the other members find it

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube  LinkedIn

Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

Anonymous
Not applicable

Spoiler
@Fowmy Can you check now?

@Anonymous 

I created a calendar table linked to both tables, you measure is simple now.

You can download the file: HERE



Measure 5 = 
VAR _Hours= Sum(Table1[Hours])
VAR _Pallets= SUM(Table2[Total Pallets])
RETURN
DIVIDE(_Pallets,_Hours)

Fowmy_0-1601496135977.png

________________________

If my answer was helpful, please consider Accept it as the solution to help the other members find it

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube  LinkedIn

 



Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

Anonymous
Not applicable

Thanks. It worked. 

 

Could you explain me why you had to create a calender table?

 

Thank you, 

Varsha

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.