Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 |
Solved! Go to 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
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
117 | |
104 | |
77 | |
73 | |
52 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |