Forum Discussion
Need help with calculating common data using Dax
Hi ,
I am new to Power bi. There is a requirement to calculate the average of amount for the common part numbers. I am not sure how to achieve it using dax query.
Table1:
| Country Code |
| AT |
| JP |
| FR |
Table 2:
| Country Code | Part Number | Amount |
| AT | 1 | 10 |
| AT | 2 | 20 |
| AT | 3 | 30 |
| AT | 4 | 40 |
| AT | 5 | 50 |
| JP | 1 | 15 |
| JP | 2 | 25 |
| FR | 3 | 30 |
| FR | 4 | 40 |
Table1 is a slicer, if the user selects a country code , that country code becomes benchmark country.
Suppose user selects AT , then benchmark country is AT . All the part numbers, amount from other countries are compared against AT
If we take AT and JP where AT is benchmark, Common part numbers are 1, 2 between AT and JP. I need the average as below.
| Country Code | Average Amount |
| AT | 15 |
| JP | 20 |
Similarly, for AT and FR, where AT is benchmark, Common part numbers are 3, 4 between AT and FR. I need the average as below.
| Country Code | Average Amount |
| AT | 35 |
| FR | 35 |
Please guide me if this is possible or not.
Make sure that there is no relationship between Table1 and Table2, then create the below measure
Avg Amount = var benchmark = SELECTEDVALUE(Table1[Country Code]) return IF( NOT ISBLANK( benchmark), var benchmarkParts = CALCULATETABLE( VALUES(Table2[Part Number]), REMOVEFILTERS(Table2[Country Code]), Table2[Country Code] = benchmark) var currentCountryParts = VALUES(Table2[Part Number]) return CALCULATE( AVERAGE(Table2[Amount]), INTERSECT( benchmarkParts, currentCountryParts ) ) )Add that to a table visual with the country code column from Table2
3 Replies
Make sure that there is no relationship between Table1 and Table2, then create the below measure
Avg Amount = var benchmark = SELECTEDVALUE(Table1[Country Code]) return IF( NOT ISBLANK( benchmark), var benchmarkParts = CALCULATETABLE( VALUES(Table2[Part Number]), REMOVEFILTERS(Table2[Country Code]), Table2[Country Code] = benchmark) var currentCountryParts = VALUES(Table2[Part Number]) return CALCULATE( AVERAGE(Table2[Amount]), INTERSECT( benchmarkParts, currentCountryParts ) ) )Add that to a table visual with the country code column from Table2
Is it possible to get the average amount for the common part numbers for all the countries in Table 2 as below using intersect?
Table 2
Country Code Part Number Amount AT 1 10 AT 2 20 AT 3 30 AT 4 40 AT 5 50 JP 1 15 JP 2 25 FR 2 30 FR 4 40 1 & 2 are common part numbers in all AT, JP, FR where AT is the benchmark country.
Is it possible to get a result like below using the same intersect dax?
Country Code Average Amount AT 15 JP 20 FR 30