Stairway to DAX and Power BI - Level 7: Function / Iterator Function Pairs: The DAX AVERAGE() and AVERAGEX() Functions

  • Thanks Bill! And keep them coming! Well done.


  • Hi Bill,

    thanks for the Series. I've struggling with an odd problem with comparing the results in Excel. When I copy the filtered results across and apply the Auto-Average Function I get a higher value then I do using PowerPivot. I added the values and the sum is also higher in Excel than PowerPivot. I then created a simple pivot table on the Re-seller sales amount filtered by the Order Date Key and that returned the same value as PowerPivot. Could you shed any light on why this might be?

    On two occasions I have been asked, "Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?" ... I am not able rightly to apprehend the kind of confusion of ideas that could provoke such a question.
    —Charles Babbage, Passages from the Life of a Philosopher

    How to post a question to get the most help

  • Thanks Bill for very interesting and very detailed lessons.

    But functions like DISTINCT, BLANK, SUM are relatively easy and trivial. Everyone who have ever worked with Excel or SQL will quickly figure out how to use them, even it's not so difficult to figure out the differences between SUM and SUMX.

    But the really difficult functions that I expect to learn them here in Stairways are ALL, ROLLUP, CALCULATE, CALCULATETABLE, ADDCOLUMNS, ALLEXCEPT, FILTER, and especially combinations of them, the proper order of nesting, and how to use them and convert from EVALUATE context in SSMS to calculated dimensions and measures in model design.


  • Thanks for your input.

    I've had lots of e-mails / other correspondence indicating that many want to "start out slowly." For that matter, I jumped ahead to more advanced scenarios (in the opinion of some readers) on a couple of articles in the MDX Stairway (as well as other places), as I like to write about the more sophisticated stuff sometimes (stuff I do in everyday consulting) - and got complaints for not being "progressive: starting with simple," in a few cases ... I have all the functions you mention, and many more sophisticated uses of DAX, on the roadmap for this series, which will be around for some time, by the look of it.

    Please feel free to explore the myriad blogs on the specific topics you seek in the meantime!

    Thanks for reading.


    William E. Pearson III


    Business Intelligence Architect

    MS SQL Server MVP and SSAS Maestro

    D & B MVP

    Island Technologies Inc. ®

    931 Monroe Drive, NE

    Suite 102-321

    Atlanta, GA 30308

    404.317.8469 Mobile

    Profile: Bill Pearson

    ----- ----------- ----------- --------------- ------------

    Publisher Sites:

    Microsoft SQL Server MVP Profile

  • Appreciate this stairway is 3 years old....being somewhat naive on this subject, is the information still relevant today with MS Power BI (2017)?

    you can lead a user to data....but you cannot make them think
    and remember....every day is a school day

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply