Getting sum of costs from one table, sales from another.

[croberts21]

I have Free PBI Desktop v2.105 (64 bit free version May 2022) on 64-bit Windows Server 2019 Datacenter v1809.

I think I have a case of disconnected tables. Here are the basic table layouts with relevant fields: 

Table: Test2022

Fields: Cost date, Total Cost

Table: Invoice

Fields: invoicedate, invoiceamt

The tables are not linked with any other field. But the dates are common between the 2 tables. 

For each month I want to show a row in a table. In each row will be the month name, the costs for that month from Test2022[Total Cost], and the sales for that month from Invoice[invoiceamt]. Both tables have a date in them. I've never done this before and have watched a short video on this but didn't understand it. 

The output table would look like this: 

Month    Costs     Sales    Pct of Sales

Jun 2021  100   1000   10.0%

Jul 2021   150   35  23.3%

Aug 2021  85   305   27.9%

The "Pct of sales" would be calculated as DIVIDE(Costs, Sales). 

Any idea what functions I should look at? This is a totally new calculation for me. 

Thank you!

[v-yanjiang-msft · Accepted solution]

Hi @croberts21 ,

According to your description "The tables are not linked with any other field. But the dates are common between the 2 tables. ", not sure if the two tables are related with each other.

A. If the two tables don't have relationship between each other, here's my solution.

Create two measures.

Sales =
MAXX (
    FILTER (
        ALL ( 'Invoice' ),
        EOMONTH ( 'Invoice'[Invoicedate], 0 )
            = EOMONTH ( MAX ( 'Test2022'[Cost date] ), 0 )
            && YEAR ( 'Invoice'[Invoicedate] ) = YEAR ( MAX ( 'Test2022'[Cost date] ) )
    ),
    'Invoice'[Invoiceamt]
)

Pct of Sales = DIVIDE(MAX('Test2022'[Total Cost]),[Sales])

Put the Test2022 table and the two measures in the visual, get the result.

B. If the two tables have relationship with the date column, here's my solution.

Create a measure.

Pct of  Sales2 =
DIVIDE ( MAX ( 'Test2022'[Total Cost] ), MAX ( 'Invoice'[Invoiceamt] ) )

Put the Test2022 table, the Invoiceamt column and the measure in the visual, get the result.

I attach my sample below for reference.

Best Regards,
Community Support Team _ kalyj

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.