Help with a DAX

[Anonymous]

I create a DAX to get an average at the end of my table,(Without this dax I get the result in between every columns) eventhough when I add a filter to my formula Im not getting the right average. Need help with that.

Im trying to add a filter which keep the weekends and holydays out. (I have another calculated column where it shows "7" for weekend or holiday).

My actuual formula sum and divide by days as it showed in the pic.

My desire result is to sum everything but divide by the labour days.

Avg Total 2 =
VAR Months =
    COUNTROWS ( ALLSELECTED ( PBI[Fecha] ) )
RETURN
    IF (
        ISINSCOPE ( PBI[Fecha] );
        SUM ( PBI[Total] );
        DIVIDE ( SUM ( PBI[Total] ); Months )
    )

Getting the result:

For example "first row" sum 5 and divided by 18 days we get 0,28 which is correct.

The disired result is the sum of every day even weekends and holidays but divided by labour day that in this same case with first row we have 5 and taking weekends, and holidays out we have 13 days: ej: 5/13 = 0.38. 

sum the row and divide buy the days...in the first row is 5 divided by 18 days=0,28

[v-juanli-msft]

Hi @Anonymous 

Create a holiday table with dates when is a holiday, then create a calculated column in your table,

if holiday = IF(ISBLANK(LOOKUPVALUE(holiday[date],holiday[date],'Table'[date])),"not holiday","holiday")

Create a column to define if a day is weekday (1-Monday to 5-Friday) or weekend( 6-Saturday, 7-Sunday),

day = WEEKDAY([date],2)

Finally, create a column, 

labour days = CALCULATE(COUNT('Table'[date]),FILTER(ALLEXCEPT('Table','Table'[cate]),[day]<6&&[if holiday]="not holiday"))

then a measure

Measure = IF(ISINSCOPE('Table'[date]),SUM('Table'[value]),SUM('Table'[value])/MAX('Table'[labour days]))

Best Regards
Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous · Accepted solution]

hi @v-juanli-msft thank you for helping me out...I think My model has something it doesnt allows me to:

2 things:

1) in the picture I cant get the column with all 13 days...it changes

2) Holiday only recognize the first holiday of the table...Could you take a look.

You get my idea though....your example gives what I need;

Sample Data:

pbix:

https://www.dropbox.com/s/7jtgn0e65ongubv/BI%20VISITAS%20DEMO%20forum.pbix?dl=0

[v-juanli-msft]

Hi @Anonymous 

For different "matricula", the "Working days total" is different.

Best Regards
Maggie