My sumx only works when I filter the table

[LeEthOven]

Hello all,

A bit of context :

I am currently trying to visualize the theoretical number of hours worked per month per employee. For that, I used a calendar function , which generated a table 'Calendar' with every day of the year since 2002, up until 2032 among other info (day of week, month of year, year, etc.). From that table, I created a function separating the working days from the holidays, characterizing the holidays as 0, and the working days as 1. The result is a number, not a text. I also added another function to isolate the holidays, which worked.

From that data, I then used the information I have to calculate the number of hours worked per day in the table 'DATAS'. 

Now, onto the gist of my problem : I am trying to calculate the number of hours worked per month by multiplying the monthly working days and the number of hours worked per day. The latter data is already shown in the original file. The former needs to be pulled with a formula akin to a sumifs. After searching online, I am using the following : 

Nombre de jours travaillés = SUMX(FILTER('Calendar';'Calendar'[Période]=DATAS[Période]);'Calendar'[IsAWorkingDay]))

Which generates a number I am guessing is the total amount of the whole "[IsAWorkingDay]" (5339 working days). However, whenever I am filtering the DATAS table, I am finding a different number, as shown in the following pictures (first one is unfiltered, second one is filtered) : 

I also tried another formula, which didn't work.

Nombre de jours travaillés = CALCULATE(SUM('Calendar'[IsAWorkingDay]);FILTER('Calendar';'Calendar'[Période]='DATAS'[Période])))

 The following didn't work either.

Nombre de jours travaillés = CALCULATE(SUM('Calendar'[IsAWorkingDay]);FILTER('Calendar';EARLIER('Calendar'[Période]='DATAS'[Période]))))​

The weird thing is, I used a formula which worked a couple weeks ago, I feel like I'm missing something obvious here !

Can you please help ?

Many thanks

LeEthOven

[johnt75 · Accepted solution]

If you have a one-to-many relationship from your calendar table to the DATAS table, then I think you can use a measure like

Total hours worked =
SUMX (
    VALUES ( 'Calendar'[Date] ),
    'Calendar'[Is working day]
        * SUMX ( RELATEDTABLE ( DATAS ), DATAS[Hours worked] )
)

which should give the hours worked in any given period - day, week, month etc.

[LeEthOven]

Unfortunately, it's a many-to-many relationship between the two periods...

However I think I've found the solution, the connection between the two tabs wasn't correctly set, therefore PBI couldn't correctly read the "FILTER" part of the formula.

All good in the end 🙂

[visheshjain]

Hi @LeEthOven,

Just a word of caution.

Many to many relationships are not advisable in your data model until you know what you are doing, as they might give unexpected results.

Thank you,

Vishesh Jain

[visheshjain]

Hi @LeEthOven,

Do you mind sharing your file with the community?

The number of working days in your first screenshot is wrong, but still the multiplication should work and not give you an error, since we are multiplying numbers.

Also I think that the a simple sum of the working days of the calendar column, should work in your fact table, as we are working with a calculated column and the row context will filter 5339 working days to its actual number for the month.

Give it a shot, if works great and if it does not then please share your file.

Thank you,

Vishesh Jain