DAX Calculations

[dadorno]

Before I begin, any help is greatly appreciated, so thank you in advance.  

I would like to calculate the average cost and total amount, by ID, where ID's from File 1, match to File 2, but where the Companies are not alike.  So in the below example, ID 1 would calculate an average cost of 12.5 (10+15) and total of 16,000.  ID 2 would have 25 and 20,000.  (Company A excluded from the math in File 2).  

I know I need a relationship between the tables by ID.  Beyond that, not sure how to get the DAX to work.  The end goal would be to have this automated, so that I can replace File 1 with any single company information.  

Thanks again 

File 1
Company	ID
A	1
A	2
A	3
A	4
A	5

File 2
Company	ID	Cost	Amount
A	1	10	4000
B	1	10	6000
C	1	15	10000
A	2	20	2000
B	2	25	20000

[v-yulgu-msft · Accepted solution]

Hi @dadorno ,

Establish a one to many relatioship between two tables based on [ID] column.

Create measures as follows.

Total Cost =
CALCULATE (
    SUM ( File2[Cost] ),
    FILTER (
        File2,
        File2[Company] <> LOOKUPVALUE ( File1[Company], File1[ID], File2[ID] )
    )
)

Average cost =
[Total Cost]
    / CALCULATE (
        COUNT ( File2[ID] ),
        FILTER (
            File2,
            File2[Company] <> LOOKUPVALUE ( File1[Company], File1[ID], File2[ID] )
        )
    )

Total Amount =
CALCULATE (
    SUM ( File2[Amount] ),
    FILTER (
        File2,
        File2[Company] <> LOOKUPVALUE ( File1[Company], File1[ID], File2[ID] )
    )
)

Best regards,

Yuliana Gu