Weighted Average with Filters

[Mike ChurchParticipant Started: 7Replies: 24Total: 31]

I am trying to build a weighted avg. measure using the SUMX example provided here. I need to do some filtering though within the measure to exclude certain rows based on date and a code.  I would have assumed that I could have utilized a formula like this to give me the results I needed, however I am coming up with an error message.   Here is an example of my code, =CALCULATE(SUMX(mytable[$ value]*mytable[# code])/SUM(mytable[$ value]),FILTER(ALL(mytable),mytable[Last Refresh Date]=EARLIER(mytable[Last Refresh Date]) && mytable[special code]=”YES”))

[tomallanKeymaster Started: 0Replies: 417Total: 417]

Hello sans holo,

What does the error message say?  My guess is if you are writing a measure and not a calculated column, the error message may say something about the use of EARLIER, but let me know…

Also, is the LastRefreshDate column actively related to the “date” column of a calendar table?

[Mike ChurchParticipant Started: 7Replies: 24Total: 31]

You are correct, it was the use of earlier and an issue with my SUMX structure.  SUMX was missing the table reference and EARLIER was not the correct tool for the job.  I switched out earlier for MAX and got the formula to validate with no errors.  Dropped it into the Values frame and ran into another issue which was that the measure output was the same for each row in my pivot table.  I removed the ALL(mytable) and voila, I think I have what I need.  Still need to do some testing but the results pass the eyeball test for now.  Here is my final result for weighted average with filters with deletions showing with strikethrough and additions to the formula showing as bold.

=CALCULATE(SUMX(mytable,mytable[$ value]*mytable[# code])/SUM(mytable[$ value]),FILTER(ALL(mytable),mytable[Last Refresh Date]=EARLIER(MAX(mytable[Last Refresh Date]) && mytable[special code]=”YES”))

 

Thanks for the direction.

[tomallanKeymaster Started: 0Replies: 417Total: 417]

Hello sans holo,

Glad to hear you were able to resolve your issue.

Since some of your formulas will be long, I wanted to introduce you to a site that offers a (free) DAX formatting service:

http://www.daxformatter.com/

I use daxformatter.com almost every day for those longer formulas that I want as readable as possible, especially when I might be revisiting them at a later date.  It has been a real time-saver for me.

http://www.daxformatter.com/

I have attached a text file that has your formula run through the DAX formatting service.

Attachments:

You must be logged in to view attached files.

[Author]

Posts