BIG HELP TO CREATE A MEASURE PLEASEEEEEEEEEEEEE!!!!!!

[Anonymous]

Who can help me to create a formula...Im going crazy trying to: (Data sample at the end)

I need to create a measure or calculated column that allows me to have a total column in a matrix like the picture:

My best try was:

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

 the result of this formula is almost good:

what I need this formula to do is Sum the ammount per day and divide by the ammount of days: I f we see in the example first row it sums 5 and divide for the ammount of days in sight (18) and I get 0,28 (First Row) and thats correct!!!. But I need the same but taking the weekends and holidays out: 

Anybody could help me out please!!! I would be so happy!!!

this is my table sample:

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

[Greg_Deckler]

See if this helps:

https://community.powerbi.com/t5/Quick-Measures-Gallery/Net-Work-Days/m-p/367362#M109

[d_gosbell]

I'm really not sure I understand your question. Are you saying that you want to filter the Months variable so that excludes rows where D_Labourales = 7? If so something like the following should work:

Avg Total 2 = 
VAR Months =
    CALCULATE( COUNTROWS ( ALLSELECTED ( PBI[Fecha] ) ), PBI[D_Labourles] <> 7)
VAR Total = SUM( PBI[Total]
RETURN
    IF (
        ISINSCOPE ( PBI[Fecha] );
        Total] ;
        DIVIDE ( Total ; Months )
    )

[Anonymous · Accepted solution]

Hi @d_gosbell,

Thank you for helping me out: and yes, that what Im trying to do but it doesnt work:

This part of the formula :

VAR Months = CALCULATE( COUNTROWS ( ALLSELECTED ( PBI[Fecha] ) )

 it gives me 18, so what you did:

VAR Months = CALCULATE( COUNTROWS ( ALLSELECTED ( PBI[Fecha] ) ), PBI[D_Labourles] <> 7)

It should give me 13 but still 18 dont know why.

Here is the data sample and with an example in excel with the desire result:

EXCEL EXAMPLE

https://www.dropbox.com/s/r7eih1bzsw76edo/DISIRED%20OUTPUT.xlsx?dl=0

PBIX SAMPLE DATA

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

[d_gosbell]

---

@Anonymous wrote:

It should give me 13 but still 18 dont know why.

---

Actually I think this is because the ALLSELECTED is stripping off the filter we are trying to apply on the D_labourales column.

VAR Months = CALCULATE( COUNTROWS ( ALLSELECTED ( PBI[Fecha], PBI[D_Labourales] ) ), PBI[D_Labourales] <> 7)

 So changing this line to the one above should fix this. 

[Anonymous]

Oh man, you dont know how greatful Iam... sometimes I get trapped and it is so frustrating...do you recomend any book to learn like a boss?

Thank you for all your help!

[d_gosbell]

I like "The Definitive Guide to DAX" by Marco Russo and Alberto Ferrari. And I think Phil Seamark and Matt Allington also have books on DAX (I know all these authors and they all know DAX really well)