cancel
Showing results for
Did you mean:
Highlighted
Occasional Visitor

## DAX Calculations

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
1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team

## Re: DAX Calculations

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

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

## Re: DAX Calculations

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

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