How to get average networkdays

[konradjonsson]

Hi.

I have an issue with how my measure is aggregated in a hierarchy.

I have created a measure that gives me the working days per country.

When presenting this in e.g. a matrix visual, the working days render correctly per country.

'Workdays' in picture above is using the regular networkdays-function:

Networkdays, Nov 2022: 22

(no holidays considered)

The problem appears when the countries are summarized in a regional hierarchy.

-it seems like that, on regional level, the working days are reduced with all unique holiday dates

The table having the holidays looks like follows

RESULT – WHICH IS “INCORRECT” ON REGION LEVEL

AMR: 17 (Nov 2, 11, 15, 24, 25)

-US: 19 (11, 24, 25)

-CAN: 21 (11)

-BRZ: 20 (2, 15)

WHAT I WOULD LIKE TO ACHIEVE

I would like the result to be the average of the included countries.

In the example above, AMR should then be 20 ((19 + 21 + 20) / 3).

[v-yangliu-msft · Accepted solution]

Hi  @konradjonsson ,

I created some data:

You can use the composition IF + ISINSCOPE() to implement the calculation of the different levels of the matrix.

IF function (DAX) - DAX | Microsoft Learn

ISINSCOPE function (DAX) - DAX | Microsoft Learn

Here are the steps you can follow：

1. Create measure.

Here we have [Group2] and [Group3] to calculate, the remaining two levels can be written directly into the formula to calculate the corresponding level, here Group1 = 1, Total = 0

Flag =
IF(
ISINSCOPE('Table'[Group3]),[Workdays V2],
IF(     ISINSCOPE('Table'[Group2])&&NOT(ISINSCOPE('Table'[Group3])),AVERAGEX(FILTER(ALL('Table'),'Table'[Group2]=MAX('Table'[Group2])),[Workdays V2]),
     IF(   ISINSCOPE('Table'[Group1])&&NOT(ISINSCOPE('Table'[Group2]))&&NOT(ISINSCOPE('Table'[Group3])),1,
0)))

2. Result:

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[konradjonsson]

Thank you Liu Yang!

Your suggestion solved my problem.

(another suggestion, from an another user, was to include "averagex(values(..." around the networkdays-function, but that did not help in my dataset)

[konradjonsson]

Thanks. Will try suggestion later.

[amitchandak]

@konradjonsson , You need to create a measure like

AvergaeX(Values(Table[Region]), calculate(networkdays(Startofmonth('Date-Main'[WkDate]), Endofmonth('Date-Main'[WkDate]), 1,Holidays) ))

[konradjonsson]

Hi Amit.

Thank you for your input! It did however not solve the problem in my dataset. I used the suggestion, from Liu Yang, that worked.