calculate average for all month dates

[Anonymous]

Hi,

when I click any month on slicer I want to calculate avg of every month sales,(for ex: June I have 5 days so I need avg of

{sum(5 days)/count(5 days)} 

Channel Partners = (132482.64+0+18540.00+0+30216.00)/5 = 36247.728

regards,

Naveen

[amitchandak · Accepted solution]

@Anonymous , ah, you are considering distinct dates across different segments. Try

measure =
divide(sum(Table[sales]),calculate(distinctcount(Table[date]),allexcept(table,table[segment])))

measure =
divide(sum(Table[sales]),calculate(distinctcount(Table[date]),alle(table[segment])))

[amitchandak]

@Anonymous , Try like

measure =
divide(sum(Table[sales]),distinctcount(Table[date]))

or

Measure =
var _max = datediff(minx(allselected('Table'),'Table'[Date]),maxx(allselected('Table'),'Table'[Date]),day)\
return
divide(sum('Table'[sales]),_max)

[Anonymous]

hi @amitchandak ,

thanks for quick replay,

here, blank value dates column are not taking this measure -->(132482.64+18540.00+30216)/3 = 60412.88 

i want like this value --> (132482.64+0+18540.00+0+30216)/5 = 36247.728

[amitchandak]

@Anonymous , Try 2nd one this option

Measure =
divide(sum('Table'[sales]),datediff(eomonth(min('Table'[Date]),-1)+1,max('Table'[Date]),day))

or

Measure =
divide(sum('Table'[sales]),datediff(min('Table'[Date]),max('Table'[Date]),day))

[Anonymous]

Hi @amitchandak 

Ex:- (132482.64+0+18540.00+0+30216)/5 = 36247.728

the measure will be showing the wrong average - 15103

[Anonymous]

Ex:- (132482.64+0+18540.00+0+30216)/5 = 36247.728

the measure will be showing the wrong average - 15103

[v-xicai · Accepted solution]

Hi @Anonymous ,

You may create measure like DAX below.

Channel Partners=

Var _TotalSum= CALCULATE(SUM(financials[Sales]), ALLSELECTED(financials))

Var _TotalCount= CALCULATE(DISTINCTCOUNT(financials[Date]), ALLSELECTED(financials))

Return

DIVIDE(_TotalSum, _TotalCount, 0)

Best Regards,

Amy 

Community Support Team _ Amy

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak · Accepted solution]

@Anonymous , ah, you are considering distinct dates across different segments. Try

measure =
divide(sum(Table[sales]),calculate(distinctcount(Table[date]),allexcept(table,table[segment])))

measure =
divide(sum(Table[sales]),calculate(distinctcount(Table[date]),alle(table[segment])))