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

  • Bill Pearson

    Ten Centuries

    Points: 1349

  • Francis Apel

    SSC Eights!

    Points: 918

    Thanks Bill! And keep them coming! Well done.

    Francis
    -----------------
    SQLRanger.com

  • Neil Burton

    SSC-Insane

    Points: 21900

    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 [/url]

  • SQL Guy 1

    SSCoach

    Points: 15726

    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

  • Bill Pearson

    Ten Centuries

    Points: 1349

    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.

    Bill

    William E. Pearson III

    CPA, CITP, CGMA, CMA, CIA, MCSE, MCDBA

    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

    wep3@islandtechnologies.com

    wep3@msas-architect.com

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

    Publisher Sites:

    Microsoft SQL Server MVP Profile

    http://www.sqlservercentral.com/stairway/72404/

    http://www.databasejournal.com/features/mssql/article.php/1459531

    http://www.msbicentral.com/Articles.aspx

    http://www.microsoft.com/MSPress/books/authors/auth11190.aspx

    http://www.sql-server-performance.com/articles_main.aspx?authid=wpearson

    http://www.informit.com/authors/bio.aspx?a=862acd62-4662-49ae-879d-541c8b4d656f

  • J Livingston SQL

    SSC Guru

    Points: 51272

    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