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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
PBI_newuser
Post Prodigy
Post Prodigy

How to combine 2 tables into 1

Hi, I want to calculate the total expense per job by month based on the below 2 tables.

For example, the total expense for WM in January is $100 and Job count in January is 2. Hence, $100/2 = $50.

 

For Product = Non-WM, I would like to take the total expense divide by Total Job Count for Product = WM.

Eg. In March, total expense for Non-WM is $300 and total job count for WM = 1, so the expense per job = $300/1 = $300

Same goes to Product = Non-REF, I would take the total job count for product = REF as denominator.

 

Table A:

DateProductEmployeeExpense
01/01/2021WMABC100
02/25/2021REFBCD200
03/24/2021Non-WMCDE300
05/12/2021Non-REFEFG400

 

Table B:

DateJob No.Product
01/05/2021Job-123WM
01/20/2021Job-234WM
02/12/2021Job-345WM
03/23/2021Job-456REF
05/03/2021Job-457REF
1 ACCEPTED SOLUTION
PaulDBrown
Community Champion
Community Champion

I'm not sure if this is what you are after, and I have had to modify your tables to showcase the measures, but here we go...

With this model:
Model.JPG

 and these measures:

Sum Expense = SUM('Table A'[Expense])
Job Number = DISTINCTCOUNT('Table B'[Job No.])
Expense per job = 
VAR NumJobs = DISTINCTCOUNT('Table B'[Job No.])
RETURN
DIVIDE([Sum Expense], NumJobs)
Non-REF from REF =
VAR REF =
    CALCULATE (
        [Job Number],
        'Dim Product'[Product] = "REF",
        ALL ( 'Dim Product' )
    )
RETURN
    IF (
        SELECTEDVALUE ( 'Dim Product'[Product] ) = "Non-REF",
        DIVIDE ( [Sum Expense], REF )
    )
Non-WM from WM =
VAR WM =
    CALCULATE ( [Job Number], 'Dim Product'[Product] = "WM", ALL ( 'Dim Product' ) )
RETURN
    IF (
        SELECTEDVALUE ( 'Dim Product'[Product] ) = "Non-WM",
        DIVIDE ( [Sum Expense], WM )
    )

You get this...

result.JPG

 

I've attached the sample PBIX file





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






View solution in original post

2 REPLIES 2
PaulDBrown
Community Champion
Community Champion

I'm not sure if this is what you are after, and I have had to modify your tables to showcase the measures, but here we go...

With this model:
Model.JPG

 and these measures:

Sum Expense = SUM('Table A'[Expense])
Job Number = DISTINCTCOUNT('Table B'[Job No.])
Expense per job = 
VAR NumJobs = DISTINCTCOUNT('Table B'[Job No.])
RETURN
DIVIDE([Sum Expense], NumJobs)
Non-REF from REF =
VAR REF =
    CALCULATE (
        [Job Number],
        'Dim Product'[Product] = "REF",
        ALL ( 'Dim Product' )
    )
RETURN
    IF (
        SELECTEDVALUE ( 'Dim Product'[Product] ) = "Non-REF",
        DIVIDE ( [Sum Expense], REF )
    )
Non-WM from WM =
VAR WM =
    CALCULATE ( [Job Number], 'Dim Product'[Product] = "WM", ALL ( 'Dim Product' ) )
RETURN
    IF (
        SELECTEDVALUE ( 'Dim Product'[Product] ) = "Non-WM",
        DIVIDE ( [Sum Expense], WM )
    )

You get this...

result.JPG

 

I've attached the sample PBIX file





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






v-jayw-msft
Community Support
Community Support

Hi @PBI_newuser ,

 

Why using count of WM for Non-WM and count of REF for Non-REF?

 

Best Regards,

Jay

Community Support Team _ Jay
If this post helps, then please consider Accept it as the solution
to help the other members find it.

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.