cancel
Showing results for
Did you mean:
Frequent Visitor

## SUM different columns based on selected value(s)

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.

Super User IV

@annguyenjoh 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.

---------------------------------------

##### I have a NEW book! DAX Cookbook from Packt
Over 120 DAX Recipes!

Proud to be a Super User!

Announcements

#### 2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

#### Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

#### Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.

Top Solution Authors
Top Kudoed Authors