Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
dataSeeker
Frequent Visitor

Calculate total help

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.Column C shows correct total.
6 REPLIES 6
Sergiy
Resolver II
Resolver II

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)
)

SumOfAverage.png

 

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

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)

SumOfAverage.png

 


@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)

SumOfAverage.png

 


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?

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

pbix + data files

Your sample file doesn't update the average sales when i click on a Month or Day.  Annotation 2019-05-08 110820.jpg

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.