Hallo!
I am currently working on a DAX-Measure that is supposed to calculated the Export Ratio (percentage of sales outsinde of the home country) of multinational group of companies.
Each company has it's home country. Right now the country code is just hard coded in my measure:
Export Ratio=DIVIDE(CALCULATE([Sales];all('Customer');'Customer'[County Code]<>"US");[US])
What I now want is to replace the hard coded country code with the country code of each company. The home country of each company can be found in the company-table.
I tried the following:
Export Ratio=DIVIDE(CALCULATE([Sales];all('Customer');'Customer'[County Code]<>Values('Company'[Home Country]);[US])
This however does not work since company and customer have no relationship. I would think that I need some sort of loop across each home country and to calculate the measure for that country, summing up (i.e. using a weighted average) the results.
But I have not idea how that could be implemented.
Could anyone help?
Thanks and regards
Julianwi
I am currently working on a DAX-Measure that is supposed to calculated the Export Ratio (percentage of sales outsinde of the home country) of multinational group of companies.
Each company has it's home country. Right now the country code is just hard coded in my measure:
Export Ratio=DIVIDE(CALCULATE([Sales];all('Customer');'Customer'[County Code]<>"US");[US])
What I now want is to replace the hard coded country code with the country code of each company. The home country of each company can be found in the company-table.
I tried the following:
Export Ratio=DIVIDE(CALCULATE([Sales];all('Customer');'Customer'[County Code]<>Values('Company'[Home Country]);[US])
This however does not work since company and customer have no relationship. I would think that I need some sort of loop across each home country and to calculate the measure for that country, summing up (i.e. using a weighted average) the results.
But I have not idea how that could be implemented.
Could anyone help?
Thanks and regards
Julianwi
