Creating Measure for average yearly & monthly sales for different countries based on working days

[beheshtifarid]

Hi everyone,

I want to create a measure which calculates sales based on working days in different countries (three country: DE,AT,CH).

There are two major tables for sales and calender in my data set.

The sales table includes all information about orders in each country and calender table includes all dates and three binary columns for working days in each country.

Sales Table:

date	country	Sales
01.01.2022	AT	20€
01.01.2022	AT	50€
01.01.2022	CH	10€
02.01.2022	DE	20€
...	...	...

Caleder Table:

date	working_day_DE	working_day_AT	working_day_CH
01.01.2022	0	1	1
02.01.2022	1	0	0
03..01.2022	1	1	1
...	...	...	...

Now I want to create a measure which calculates avrage of Sales in each country divided by SUM of working days in year/month.

I would appreciate, if someone can help me with this issue.

[amitchandak]

@beheshtifarid , Try a new measure like

AverageX(Values(Sales[Country]), calculate(Sum(Sales[Sales]) , filter(calendar, calendar[working_day_DE] =1)))