Sum and divide DOW based on month filtering

[PrizzC15]

Hi all,

I need helps as below:

I had tried to upload the pbix file, but fail.

[v-yangliu-msft · Accepted solution]

Hi  @PrizzC15 ,

I created some data:

Here are the steps you can follow：

1. Create measure.

sum divde =
var _selectYear=SELECTEDVALUE('Table'[Year])
var _selectmonthname=SELECTEDVALUE('Table'[Month Name])
var _month=MAXX(FILTER(ALL('Table'),'Table'[Year]=_selectYear&&'Table'[Month Name]=_selectmonthname),[Month])
var _count=COUNTX(FILTER(ALL('Table'),
'Table'[Year]=MAX('Table'[Year])&&'Table'[Month]=MAX('Table'[Month])&&'Table'[Day]=MAX('Table'[Day])),[Day])
var _sum=SUMX(FILTER(ALL('Table'),'Table'[Day]=MAX('Table'[Day])&&'Table'[Month]=_month),[Value])
return
DIVIDE(_sum,_count)

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

[PrizzC15]

Hi @v-yangliu-msft 

Im trying to create the measure that you provided but I get something as below:

Measure =

var _selectYear=SELECTEDVALUE(ODS_Labor_Hours[Year])

var _selectmonthname=SELECTEDVALUE(ODS_Labor_Hours[Month Name])

var _month=MAXX(FILTER(ALL(ODS_Labor_Hours),ODS_Labor_Hours[Year]=_selectYear&&ODS_Labor_Hours[Month Name]=_selectmonthname),[Month Name])

var _count=COUNTX(FILTER(ALL(ODS_Labor_Hours),

ODS_Labor_Hours[Year]=MAX(ODS_Labor_Hours[Year])&&ODS_Labor_Hours[Month Name]=MAX(ODS_Labor_Hours[Month Name])&&ODS_Labor_Hours[Day Name]=MAX(ODS_Labor_Hours[Day Name])),[Day Name])

var _sum=SUMX(FILTER(ALL(ODS_Labor_Hours),ODS_Labor_Hours[Day Name]=MAX(ODS_Labor_Hours[Day Name])&&ODS_Labor_Hours[Month Name]=_month),[VALUE])

return

DIVIDE(_sum,_count)

[Mahesh0016]

Did I answer your question? Mark my post as a solution! Appreciate your Kudos !! 

[FarhanAhmed]

Create a relationship between your table using composite key "Month-Day"

Then try to create DAX

_Avg Day Sales = CALCULATE( Divide(SUM([Sales),SUM(Table 2 [Count]),BLANK()))