SUM different columns based on selected value(s)

[Anonymous]

Hi,

I have a Date table like this

Date	Year-Month	Sweden	Denmark	Norway
2020-01-01	2020-01	0	1	0
2020-01-02	2020-01	0	1	0
2020-01-03	2020-01	1	1	0
2020-01-04	2020-01	1	1	1
2020-01-05	2020-01	0	0	1
2020-01-06	2020-01	0	0	1
2020-01-07	2020-01	1	1	1
2020-01-08	2020-01	1	1	0
2020-01-09	2020-01	1	1	1
2020-01-10	2020-01	1	1	0

Then I have the Company table like this

Company	Country
Swedish Company	Sweden
Danish Company	Denmark
Norwegian Company	Norway

Would like to calculate Sales per working day for each company based on the total working day from the calendar. So I did the following measures:

Working day = SWITCH(TRUE(),
    SELECTEDVALUE(dimCompany[Country])="Sweden",SUM(dimDate[Sweden]),
    SELECTEDVALUE(dimCompany[Country])="Norway",SUM(dimDate[Norway]),
    SELECTEDVALUE(dimCompany[Country])="Denmark",SUM(dimDate[Denmark]),
    0)

Sales per working day = DIVIDE([Sales],[Working day],0)

It works well if I just choose one country... of course. So if I would like to choose more than one country, how should I write my DAX?

Example: If I pick Swedish and Danish Company, I want to see the result of: (SUM(dimDate[Sweden])+SUM(dimDate[Denmark]))/2

If I pick 3 companies, I want to see the result of (SUM(dimDate[Sweden])+SUM(dimDate[Denmark])+SUM(dimDate[Norway]))/3

Thank you very much.

[Greg_Deckler]

@Anonymous Well, you could use the IN operator like

"Sweden" IN DISTINCT('Company'[Country]) && "Norway" IN DISTINCT('Company'[Country])

That said, it would be far better to unpivot the country columns in your first table and create a relationship between your two tables and then there is zero need for fancy DAX.