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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
RavitPBI
Frequent Visitor

Calculation using different tables with no relationship

Table A: Outbound

Date_idOrg_idOutboundUnits
180500.00
280600.00
380700.00
186100.00
286200.00
386300.00

 

Table B: Inbound

Date_idOrg_idVendor_idInboundUnits
180150.00
180260.00
280170.00
280210.00
380120.00
380230.00
186150.00
186260.00
286170.00
286210.00
386120.00
386230.00

 

Table C: GoalUPH (Goal Units per Hour)

Date_idOrg_idGoalUPH
18020.00
28020.00
38020.00
18625.00
28625.00
38625.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!

2 ACCEPTED SOLUTIONS
amitchandak
Super User
Super User

@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.

View solution in original post

v-janeyg-msft
Community Support
Community Support

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
    )

v-janeyg-msft_0-1603416410749.png

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.

View solution in original post

4 REPLIES 4
v-janeyg-msft
Community Support
Community Support

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
    )

v-janeyg-msft_0-1603416410749.png

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!

amitchandak
Super User
Super User

@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! 🙂 

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Power BI Header Change

Power BI Header Update

Read about the exciting updates for the Power BI forum.