none
Export Ratio for companies of multiple countries

    Question

  • 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 since different companies of the same group could have a different home country.
    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 no idea how that could be implemented.
    Could anyone help?

    Thanks and regards!
     

    Sunday, September 15, 2013 10:27 PM

Answers

  • If your schema is something like this: 


    Here are some measures that will do the trick:

    Export sales for each company

    CompanyExportSales:=Calculate([Sales], FILTER(Sales, RELATED(Company[Home Country])<>RELATED(Customer[Country Code])))

    Export ratio

    ExportRatio:=[CompanyExportSales]/[Sales]

    Slicing ExportRatio on country can give you the combined ratio for all companies in the country. If you want it explicitly:

    Export sales for all companies in a country

    AllCompanyExportSales:= SUMX(VALUES(Company[Home Country]), CALCULATE([CompanyExportSales],ALL(Company[CompanyId])))

    AllCompanySales:= Calculate([Sales],ALL(Company[CompanyId]))

    AllCompanyExportRatio:=[AllCompanyExportSales]/[AllCompanySales]

    Monday, September 16, 2013 10:04 PM
    Moderator

All replies

  • If your schema is something like this: 


    Here are some measures that will do the trick:

    Export sales for each company

    CompanyExportSales:=Calculate([Sales], FILTER(Sales, RELATED(Company[Home Country])<>RELATED(Customer[Country Code])))

    Export ratio

    ExportRatio:=[CompanyExportSales]/[Sales]

    Slicing ExportRatio on country can give you the combined ratio for all companies in the country. If you want it explicitly:

    Export sales for all companies in a country

    AllCompanyExportSales:= SUMX(VALUES(Company[Home Country]), CALCULATE([CompanyExportSales],ALL(Company[CompanyId])))

    AllCompanySales:= Calculate([Sales],ALL(Company[CompanyId]))

    AllCompanyExportRatio:=[AllCompanyExportSales]/[AllCompanySales]

    Monday, September 16, 2013 10:04 PM
    Moderator
  • Thank you very much Taylor! That is working just perfectly!

    Tuesday, September 17, 2013 12:20 PM