Networkdays function impacted by chart dimension

[guster999]

Hi,

I'm having trouble with a bar chart that has 2 dims and a measure that includes a networkdays calculation.

Please see attached file and filter on Mth-Yr='Oct-17' and callsign='NEWT21'.  The table next to the chart has the correct average calculation (10.0) , but the chart does not calculate this measure correctly (11.15), although it uses the same formula.

The reason is Dim 'DHB' , value='ACC' has only only 1 weekday available for selection, due to very few data rows compared to multiple for the other DHB values.

So what I'm after is a way of dividing by networking days in the chart measure - for the user selected Mth-Yr's , but ignoring the dimension DHB - to get the same average value as per the table.

I have tried using the TOTAL function but with no success.

many thanks

Gus

[sunny_talwar · Accepted solution]

May be try this

Count(DISTINCT Ref_Number)/NetWorkDays(Min(TOTAL <Callsign> Date1), Max(TOTAL <Callsign> Date1))

[guster999]

thank you so much Sunny, that works a treat !!