Forum Discussion
BIG HELP TO CREATE A MEASURE PLEASEEEEEEEEEEEEE!!!!!!
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
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
6 Replies
- Greg_Deckler
Community Champion
- d_gosbell
Super User
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 ) )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
