Calculate total help

[dataSeeker]

I have a dax formula that is dividing the total by the count of non blank items to give me an average.  I need help  with the formula to get the total to be correct. Below is the dax measure:   Below that is a screenshot of the numbers that are created from the measure.  I added the correct amount in Column C. 

SalesSumAvg =

var nonblank = IF(ISBLANK([Sales1yrAgo]),0,1)+IF(ISBLANK([Sales2yrAgo]),0,1)+IF(ISBLANK([Sales3yrAgo]),0,1)+IF(ISBLANK([Sales4YrAgo]),0,1)

return

DIVIDE([SalesSum],nonblank,0)

Column C shows correct total.

[Sergiy]

Hi,

you need something like this:

Sum of Average Sales = 
VAR result = AVERAGEX(Sales,[Sales])
return
if(
    HASONEVALUE(Sales[Date]),
    result,
    SUMX(VALUES(Dates[Day]), result)
)

[dataSeeker]

Is the "Sum of Average Sales" a new measure or an edit to my existing measure?

[Sergiy]

Your question was:

---

@dataSeeker wrote:

I need help  with the formula to get the total to be correct  ...  I added the correct amount in Column C. 

---

So I showed an example of how to construct a measure that for each row calculates an Average but for totals instead of Average it calculates Sum.

(By the way, I have no idea how you happen to get 5225987.87 for the Totals)

[dataSeeker]

---

@Sergiy wrote:

So I showed an example of how to construct a measure that for each row calculates an Average but for totals instead of Average it calculates Sum.

(By the way, I have no idea how you happen to get 5225987.87 for the Totals)

---

Power BI calculated the 5225987.87.  I tried using your formula and it doesn't sum the total.  Do i want to check if there is one value for the date or day of the week?

[Sergiy]

You can have a look at how it works in my sample file:

pbix + data files

[dataSeeker]

Your sample file doesn't update the average sales when i click on a Month or Day.