Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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)
________________________
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 🙂
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
@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 🙂
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
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?
@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 🙂
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
Please see link below
https://drive.google.com/file/d/1-IaRCDGDavHrfj6tn2wG43YbD9DMuwoX/view?usp=drivesdk
Thank you
@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 🙂
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
@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)
________________________
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 🙂
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
Thanks. It worked.
Could you explain me why you had to create a calender table?
Thank you,
Varsha
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
104 | |
96 | |
80 | |
67 | |
62 |
User | Count |
---|---|
137 | |
106 | |
104 | |
81 | |
63 |