Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
beheshtifarid
New Member

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

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:

datecountrySales
01.01.2022AT20€
01.01.2022AT50€
01.01.2022CH10€
02.01.2022DE20€
.........

 

Caleder Table:

dateworking_day_DEworking_day_ATworking_day_CH
01.01.2022011
02.01.2022100
03..01.2022111
............

  

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.

1 REPLY 1
amitchandak
Super User
Super User

@beheshtifarid , Try a new measure like

 

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors