Total() vs Window_Sum()

Version 3

    So I was having a conversation with Dan Huff today about Window_Sum() vs Total() and he helped me build a great example showing the difference.

     

    Total() is computed ignoring the Partition and goes against the Database unlike Window_Sum() which is computed locally.  What does this mean, well.. see the attached workbook.

     

    Placing [State] and [City] on the Rows shelf provides a list of each City within each State.  Some Cities exist in many States though.  (Apparently lots of people like to live in "Springfield", "Clinton" and "Columbus" ).  The view/sheet named "Just City" shows the number of States per City.   There are 1727 combinations of City/State in the data set though.

     

    However, we have 1523 unique City name values so if the field Total(Countd([City])) is placed on the measures and computed as Table Down, the value returned is 1523.

     

    Now, to see the difference between Total and Window_Sum,  let's look at the "State and City" view/sheet that uses Total(Countd([City])) vs Window_Sum(Countd[City])) and we can see the Total() returns the 1523 ignoring the State partition.  However Window_Sum() honors the partition and returns 1727.

     

    I thought this might be a nice example for others and I didn't want to lose the workbook so I am sharing here.

     

    Thanks to Dan as well.