Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
User | Count |
---|---|
140 | |
113 | |
104 | |
76 | |
63 |
User | Count |
---|---|
135 | |
126 | |
110 | |
70 | |
61 |