Total() vs Window_Sum()

Version 5

    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.

     

    Correction to your statement. "The view/sheet named "Just City" shows the number of States per City". It does not show the number of States per City. You even named the tab "Just City"; it has nothing to do with State. It shows the number of distinct cities for each city, which will always be "1" (one city can only have itself as its own city). So I don't understand why you even made that tab. You're just causing confusion.