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.
Table A: Outbound
Date_id | Org_id | OutboundUnits |
1 | 80 | 500.00 |
2 | 80 | 600.00 |
3 | 80 | 700.00 |
1 | 86 | 100.00 |
2 | 86 | 200.00 |
3 | 86 | 300.00 |
Table B: Inbound
Date_id | Org_id | Vendor_id | InboundUnits |
1 | 80 | 1 | 50.00 |
1 | 80 | 2 | 60.00 |
2 | 80 | 1 | 70.00 |
2 | 80 | 2 | 10.00 |
3 | 80 | 1 | 20.00 |
3 | 80 | 2 | 30.00 |
1 | 86 | 1 | 50.00 |
1 | 86 | 2 | 60.00 |
2 | 86 | 1 | 70.00 |
2 | 86 | 2 | 10.00 |
3 | 86 | 1 | 20.00 |
3 | 86 | 2 | 30.00 |
Table C: GoalUPH (Goal Units per Hour)
Date_id | Org_id | GoalUPH |
1 | 80 | 20.00 |
2 | 80 | 20.00 |
3 | 80 | 20.00 |
1 | 86 | 25.00 |
2 | 86 | 25.00 |
3 | 86 | 25.00 |
My data model has the above setup of tables A, B and C (amongst many other tables). Unfortunately, there is no active/inactive relationship between these A, B, C tables (since my PowerBI desktop doesn't allow for many-to-many relationships). I am trying to achieve the following formula:
HoursNeeded = (OutboundUnits + InboundUnits) / GoalUPH --> for each combination of Date_id and Org_id.
So, for example, for Date_id = 1 and Org_id = 80, the calculation would (mathematically) go like:
HoursNeeded = (500.00 + (50.00 + 60.00)) / 20.00 = 30.5
I'd appreciate any help in creating the above calculation. Thanks!
Solved! Go to Solution.
@RavitPBI , Create two/three common dimension table date, org, and vendor. Accompany them with boards and then use it.
Refer to this video how two create one when they have one -https://www.youtube.com/watch?v=Bkf35Roman8
Provide your feedback and tips for new videos
Dax Series Tutorial Vs SQL Direct Query PBI Tips
We appreciate your congratulations.
Hi, @RavitPBI
It’s my pleasure to answer for you.
According to your description, I think you can create two slicers(column in table C) and a measure to calculate the desired result.
Like this:
HoursNeeded =
VAR dateid =
SELECTEDVALUE ( GoalUPH[Date_id] )
VAR orgid =
SELECTEDVALUE ( GoalUPH[Org_id] )
VAR sumunits =
SUMX (
FILTER ( ALL ( Outbound ), [Date_id] = dateid && [Org_id] = orgid ),
[OutboundUnits]
)
+ SUMX (
FILTER ( ALL ( Inbound ), [Date_id] = dateid && [Org_id] = orgid ),
[InboundUnits]
)
RETURN
IF (
ISFILTERED ( GoalUPH[Date_id] ) && ISFILTERED ( GoalUPH[Org_id] ),
DIVIDE ( sumunits, SELECTEDVALUE ( GoalUPH[GoalUPH] ) ),
0
)
If it doesn’t solve your problem, please feel free to ask me.
Best Regards
Janey Guo
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi, @RavitPBI
It’s my pleasure to answer for you.
According to your description, I think you can create two slicers(column in table C) and a measure to calculate the desired result.
Like this:
HoursNeeded =
VAR dateid =
SELECTEDVALUE ( GoalUPH[Date_id] )
VAR orgid =
SELECTEDVALUE ( GoalUPH[Org_id] )
VAR sumunits =
SUMX (
FILTER ( ALL ( Outbound ), [Date_id] = dateid && [Org_id] = orgid ),
[OutboundUnits]
)
+ SUMX (
FILTER ( ALL ( Inbound ), [Date_id] = dateid && [Org_id] = orgid ),
[InboundUnits]
)
RETURN
IF (
ISFILTERED ( GoalUPH[Date_id] ) && ISFILTERED ( GoalUPH[Org_id] ),
DIVIDE ( sumunits, SELECTEDVALUE ( GoalUPH[GoalUPH] ) ),
0
)
If it doesn’t solve your problem, please feel free to ask me.
Best Regards
Janey Guo
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @v-janeyg-msft,
Thanks for sharing the solution. While it seems this calculation would work with your recommendation, however, usage of slicer is not an option for me. This calculation should be performed behind the scenes without the use of any end-user interaction. Appreciate the help!
@RavitPBI , Create two/three common dimension table date, org, and vendor. Accompany them with boards and then use it.
Refer to this video how two create one when they have one -https://www.youtube.com/watch?v=Bkf35Roman8
Provide your feedback and tips for new videos
Dax Series Tutorial Vs SQL Direct Query PBI Tips
We appreciate your congratulations.
Hi @amitchandak,
Thanks for providing the right direction. The video gave some ideas to build upon my own solution using bridge tables. Appreciate the help a lot! 🙂
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 |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |