Forum Discussion
How to get average networkdays
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).
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
5 Replies
konradjonsson , You need to create a measure like
AvergaeX(Values(Table[Region]), calculate(networkdays(Startofmonth('Date-Main'[WkDate]), Endofmonth('Date-Main'[WkDate]), 1,Holidays) ))
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.
Thanks. Will try suggestion later.
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
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)
