Power BI: Calculated Fields vs. Calculated Columns
Be careful with Calculated Columns or you may end up with the wrong results. Take the following set of data (below). If you use a Calculated Column "wp", then you will get the wrong results if you wish to calculate the total win percentage for a coach. This can be seen in a Matrix of Coach, Season and "wp" or if you ask Q&A in Power BI. To get the correct answer, use a Calculated Field instead with a formula of WinningPercentage:=SUM([Wins])/SUM([Games]).
For example, a matrix of Power BI will tell you that the overall "wp" for Earle Bruce is 74.79%. However, Earle won 81 games out of 108 games and thus his true "wp" is 75% (81 / 108). The Calculated Field gets it right while the Calculated Column gets it wrong. However, for individual years, both the Calculated Column "wp" and Calculated Field "WinningPercentage" agree.
What is going on? Well, in a matrix using average of a Calculated Column, "wp", the "Total" row in the matrix is actually average of the individual rows while with a Calculated Field, "WinningPercentage" it is the result of the formula for that particular row. In the total line, the numbers used by the Calculated Field are 108 for games and 81 for Wins and thus it calculates the correct answer.
The following data can be used to recreate this scenario.
| Season | Wins | Losses | Ties | Coach | Conference | Bowl Game | Games | wp |
|---|---|---|---|---|---|---|---|---|
| 2012 | 12 | 0 | 0 | Urban Meyer | Big Ten | 12 | 100.00 % | |
| 1999 | 6 | 6 | 0 | John Cooper | Big Ten | 12 | 50.00 % | |
| 1988 | 4 | 6 | 1 | John Cooper | Big Ten | 11 | 36.36 % | |
| 1987 | 6 | 4 | 1 | Earle Bruce | Big Ten | 11 | 54.55 % | |
| 1971 | 6 | 4 | 0 | Woody Hayes | Big Ten | 10 | 60.00 % | |
| 1969 | 8 | 1 | 0 | Woody Hayes | Big Ten | 9 | 88.89 % | |
| 1967 | 6 | 3 | 0 | Woody Hayes | Big Ten | 9 | 66.67 % | |
| 1966 | 4 | 5 | 0 | Woody Hayes | Big Ten | 9 | 44.44 % | |
| 1965 | 7 | 2 | 0 | Woody Hayes | Big Ten | 9 | 77.78 % | |
| 1964 | 7 | 2 | 0 | Woody Hayes | Big Ten | 9 | 77.78 % | |
| 1963 | 5 | 3 | 1 | Woody Hayes | Big Ten | 9 | 55.56 % | |
| 1962 | 6 | 3 | 0 | Woody Hayes | Big Ten | 9 | 66.67 % | |
| 1961 | 8 | 0 | 1 | Woody Hayes | Big Ten | 9 | 88.89 % | |
| 1960 | 7 | 2 | 0 | Woody Hayes | Big Ten | 9 | 77.78 % | |
| 1959 | 3 | 5 | 1 | Woody Hayes | Big Ten | 9 | 33.33 % | |
| 1958 | 6 | 1 | 2 | Woody Hayes | Big Ten | 9 | 66.67 % | |
| 1956 | 6 | 3 | 0 | Woody Hayes | Big Ten | 9 | 66.67 % | |
| 1955 | 7 | 2 | 0 | Woody Hayes | Big Ten | 9 | 77.78 % | |
| 1953 | 6 | 3 | 0 | Woody Hayes | Big Ten | 9 | 66.67 % | |
| 1952 | 6 | 3 | 0 | Woody Hayes | Big Ten | 9 | 66.67 % | |
| 1951 | 4 | 3 | 2 | Woody Hayes | Big Ten | 9 | 44.44 % | |
| 1950 | 6 | 3 | 0 | Wes Fesler | Big Ten | 9 | 66.67 % | |
| 1948 | 6 | 3 | 0 | Wes Fesler | Big Ten | 9 | 66.67 % | |
| 1947 | 2 | 6 | 1 | Wes Fesler | Big Ten | 9 | 22.22 % | |
| 1946 | 4 | 3 | 2 | Paul Bixler | Big Ten | 9 | 44.44 % | |
| 1945 | 7 | 2 | 0 | Carroll Widdoes | Big Ten | 9 | 77.78 % | |
| 1944 | 9 | 0 | 0 | Carroll Widdoes | Big Ten | 9 | 100.00 % | |
| 1943 | 3 | 6 | 0 | Paul Brown | Big Ten | 9 | 33.33 % | |
| 1942 | 9 | 1 | 0 | Paul Brown | Big Ten | 10 | 90.00 % | |
| 1941 | 6 | 1 | 1 | Paul Brown | Big Ten | 8 | 75.00 % | |
| 1940 | 4 | 4 | 0 | Francis Schmidt | Big Ten | 8 | 50.00 % | |
| 1939 | 6 | 2 | 0 | Francis Schmidt | Big Ten | 8 | 75.00 % | |
| 1938 | 4 | 3 | 1 | Francis Schmidt | Big Ten | 8 | 50.00 % | |
| 1937 | 6 | 2 | 0 | Francis Schmidt | Big Ten | 8 | 75.00 % | |
| 1936 | 5 | 3 | 0 | Francis Schmidt | Big Ten | 8 | 62.50 % | |
| 1935 | 7 | 1 | 0 | Francis Schmidt | Big Ten | 8 | 87.50 % | |
| 1934 | 7 | 1 | 0 | Francis Schmidt | Big Ten | 8 | 87.50 % | |
| 1933 | 7 | 1 | 0 | Sam Willaman | Big Ten | 8 | 87.50 % | |
| 1932 | 4 | 1 | 3 | Sam Willaman | Big Ten | 8 | 50.00 % | |
| 1931 | 6 | 3 | 0 | Sam Willaman | Big Ten | 9 | 66.67 % | |
| 1930 | 5 | 2 | 1 | Sam Willaman | Big Ten | 8 | 62.50 % | |
| 1929 | 4 | 3 | 1 | Sam Willaman | Big Ten | 8 | 50.00 % | |
| 1928 | 5 | 2 | 1 | John Wilce | Big Ten | 8 | 62.50 % | |
| 1927 | 4 | 4 | 0 | John Wilce | Big Ten | 8 | 50.00 % | |
| 1926 | 7 | 1 | 0 | John Wilce | Big Ten | 8 | 87.50 % | |
| 1925 | 4 | 3 | 1 | John Wilce | Big Ten | 8 | 50.00 % | |
| 1924 | 2 | 3 | 3 | John Wilce | Big Ten | 8 | 25.00 % | |
| 1923 | 3 | 4 | 1 | John Wilce | Big Ten | 8 | 37.50 % | |
| 1922 | 3 | 4 | 0 | John Wilce | Big Ten | 7 | 42.86 % | |
| 1921 | 5 | 2 | 0 | John Wilce | Ohio Athletic | 7 | 71.43 % | |
| 1919 | 6 | 1 | 0 | John Wilce | Ohio Athletic | 7 | 85.71 % | |
| 1918 | 3 | 3 | 0 | John Wilce | Ohio Athletic | 6 | 50.00 % | |
| 1917 | 8 | 0 | 1 | John Wilce | Ohio Athletic | 9 | 88.89 % | |
| 1916 | 7 | 0 | 0 | John Wilce | Ohio Athletic | 7 | 100.00 % | |
| 1915 | 5 | 1 | 1 | John Wilce | Ohio Athletic | 7 | 71.43 % | |
| 1914 | 5 | 2 | 0 | John Wilce | Ohio Athletic | 7 | 71.43 % | |
| 1913 | 4 | 2 | 1 | John Wilce | Ohio Athletic | 7 | 57.14 % | |
| 1912 | 6 | 3 | 0 | John Richards | Ohio Athletic | 9 | 66.67 % | |
| 1911 | 5 | 3 | 2 | Harry Vaughn | Ohio Athletic | 10 | 50.00 % | |
| 1910 | 6 | 1 | 3 | Howard Jones | Ohio Athletic | 10 | 60.00 % | |
| 1909 | 7 | 3 | 0 | Albert Herrnstein | Ohio Athletic | 10 | 70.00 % | |
| 1908 | 6 | 4 | 0 | Albert Herrnstein | Ohio Athletic | 10 | 60.00 % | |
| 1907 | 7 | 2 | 1 | Albert Herrnstein | Ohio Athletic | 10 | 70.00 % | |
| 1906 | 8 | 1 | 0 | Albert Herrnstein | Ohio Athletic | 9 | 88.89 % | |
| 1905 | 8 | 2 | 2 | ER Sweetland | Ohio Athletic | 12 | 66.67 % | |
| 1904 | 6 | 5 | 0 | ER Sweetland | Ohio Athletic | 11 | 54.55 % | |
| 1903 | 8 | 3 | 0 | Perry Hale | Ohio Athletic | 11 | 72.73 % | |
| 1902 | 6 | 2 | 2 | Perry Hale | Ohio Athletic | 10 | 60.00 % | |
| 1901 | 5 | 3 | 1 | John Eckstorm | Independent | 9 | 55.56 % | |
| 1900 | 8 | 1 | 1 | John Eckstorm | Independent | 10 | 80.00 % | |
| 1899 | 9 | 0 | 1 | John Eckstorm | Independent | 10 | 90.00 % | |
| 1898 | 3 | 5 | 0 | Jack Ryder | Independent | 8 | 37.50 % | |
| 1897 | 1 | 8 | 0 | David Edwards | Independent | 9 | 11.11 % | |
| 1896 | 5 | 5 | 1 | Charles Hickey | Independent | 11 | 45.45 % | |
| 1895 | 4 | 4 | 2 | Jack Ryder | Independent | 10 | 40.00 % | |
| 1894 | 5 | 6 | 0 | Jack Ryder | Independent | 11 | 45.45 % | |
| 1893 | 4 | 5 | 0 | Jack Ryder | Independent | 9 | 44.44 % | |
| 1892 | 5 | 3 | 0 | Jack Ryder | Independent | 8 | 62.50 % | |
| 1891 | 2 | 2 | 0 | Alexander Lilley | Independent | 4 | 50.00 % | |
| 1890 | 1 | 3 | 0 | Alexander Lilley | Independent | 4 | 25.00 % | |
| 2014 | 14 | 1 | 0 | Urban Meyer | Big Ten | Sugar Bowl | 15 | 93.33 % |
| 2013 | 12 | 2 | 0 | Urban Meyer | Big Ten | Orange Bowl | 14 | 85.71 % |
| 2011 | 6 | 7 | 0 | Luke Fickell | Big Ten | Gator Bowl | 13 | 46.15 % |
| 2010 | 12 | 1 | 0 | Jim Tressel | Big Ten | Sugar Bowl | 13 | 92.31 % |
| 2009 | 11 | 2 | 0 | Jim Tressel | Big Ten | Rose Bowl | 13 | 84.62 % |
| 2008 | 10 | 3 | 0 | Jim Tressel | Big Ten | Fiesta Bowl | 13 | 76.92 % |
| 2007 | 11 | 2 | 0 | Jim Tressel | Big Ten | BCS National Championship | 13 | 84.62 % |
| 2006 | 12 | 1 | 0 | Jim Tressel | Big Ten | BCS National Championship | 13 | 92.31 % |
| 2005 | 10 | 2 | 0 | Jim Tressel | Big Ten | Fiesta Bowl | 12 | 83.33 % |
| 2004 | 8 | 4 | 0 | Jim Tressel | Big Ten | Alamo Bowl | 12 | 66.67 % |
| 2003 | 11 | 2 | 0 | Jim Tressel | Big Ten | Fiesta Bowl | 13 | 84.62 % |
| 2002 | 14 | 0 | 0 | Jim Tressel | Big Ten | Fiesta Bowl | 14 | 100.00 % |
| 2001 | 7 | 5 | 0 | Jim Tressel | Big Ten | Outback Bowl | 12 | 58.33 % |
| 2000 | 8 | 4 | 0 | John Cooper | Big Ten | Outback Bowl | 12 | 66.67 % |
| 1998 | 11 | 1 | 0 | John Cooper | Big Ten | Sugar Bowl | 12 | 91.67 % |
| 1997 | 10 | 3 | 0 | John Cooper | Big Ten | Sugar Bowl | 13 | 76.92 % |
| 1996 | 11 | 1 | 0 | John Cooper | Big Ten | Rose Bowl | 12 | 91.67 % |
| 1995 | 11 | 2 | 0 | John Cooper | Big Ten | Citrus Bowl | 13 | 84.62 % |
| 1994 | 9 | 4 | 0 | John Cooper | Big Ten | Florida Citrus Bowl | 13 | 69.23 % |
| 1993 | 10 | 1 | 1 | John Cooper | Big Ten | Holiday Bowl | 12 | 83.33 % |
| 1992 | 8 | 3 | 1 | John Cooper | Big Ten | Florida Citrus Bowl | 12 | 66.67 % |
| 1991 | 8 | 4 | 0 | John Cooper | Big Ten | Hall of Fame Bowl | 12 | 66.67 % |
| 1990 | 7 | 4 | 1 | John Cooper | Big Ten | Liberty Bowl | 12 | 58.33 % |
| 1989 | 8 | 4 | 0 | John Cooper | Big Ten | Hall of Fame Bowl | 12 | 66.67 % |
| 1986 | 10 | 3 | 0 | Earle Bruce | Big Ten | Cotton Bowl | 13 | 76.92 % |
| 1985 | 9 | 3 | 0 | Earle Bruce | Big Ten | Florida Citrus Bowl | 12 | 75.00 % |
| 1984 | 9 | 3 | 0 | Earle Bruce | Big Ten | Rose Bowl | 12 | 75.00 % |
| 1983 | 9 | 3 | 0 | Earle Bruce | Big Ten | Fiesta Bowl | 12 | 75.00 % |
| 1982 | 9 | 3 | 0 | Earle Bruce | Big Ten | Holiday Bowl | 12 | 75.00 % |
| 1981 | 9 | 3 | 0 | Earle Bruce | Big Ten | Liberty Bowl | 12 | 75.00 % |
| 1980 | 9 | 3 | 0 | Earle Bruce | Big Ten | Fiesta Bowl | 12 | 75.00 % |
| 1979 | 11 | 1 | 0 | Earle Bruce | Big Ten | Rose Bowl | 12 | 91.67 % |
| 1978 | 7 | 4 | 1 | Woody Hayes | Big Ten | Gator Bowl | 12 | 58.33 % |
| 1977 | 9 | 3 | 0 | Woody Hayes | Big Ten | Sugar Bowl | 12 | 75.00 % |
| 1976 | 9 | 2 | 1 | Woody Hayes | Big Ten | Orange Bowl | 12 | 75.00 % |
| 1975 | 11 | 1 | 0 | Woody Hayes | Big Ten | Rose Bowl | 12 | 91.67 % |
| 1974 | 10 | 2 | 0 | Woody Hayes | Big Ten | Rose Bowl | 12 | 83.33 % |
| 1973 | 10 | 0 | 1 | Woody Hayes | Big Ten | Rose Bowl | 11 | 90.91 % |
| 1972 | 9 | 2 | 0 | Woody Hayes | Big Ten | Rose Bowl | 11 | 81.82 % |
| 1970 | 9 | 1 | 0 | Woody Hayes | Big Ten | Rose Bowl | 10 | 90.00 % |
| 1968 | 10 | 0 | 0 | Woody Hayes | Big Ten | Rose Bowl | 10 | 100.00 % |
| 1957 | 9 | 1 | 0 | Woody Hayes | Big Ten | Rose Bowl | 10 | 90.00 % |
| 1954 | 10 | 0 | 0 | Woody Hayes | Big Ten | Rose Bowl | 10 | 100.00 % |
| 1949 | 7 | 1 | 2 | Wes Fesler | Big Ten | Rose Bowl | 10 | 70.00 % |
| 1920 | 7 | 1 | 0 | John Wilce | Ohio Athletic | Rose Bowl | 8 | 87.50 % |