Forum Discussion
Is there sth wrong with summarize function?
I Reproduce the scenario in http://www.sqlbi.com/articles/all-the-secrets-of-summarize/, but the answer is different.
anyone can explain it?
change the first product from bike to shirt, the answer is different:
Well, the Power Pivot engine in Excel 2016 is not the same as the one used by Excel 2013 (when Marco Russo wrote his article).
I have made a few tests, and here are my conclusions: this is a bug.
The long version is the behaviour no longer works as Marco Russo explained in his article. There is still an implicit filter but only on the first column in the table (at the time of creation).
If you create a new table with columns listed in this order: Quantity, Product, Color, Amount, like this:
Quantity Product Color Amount 1 Shirt Red 100 2 Shirt Red 200 2 Shirt Green 200 3 Bike Green 300 3 Shirt Green 300 3 Bike Blue 300 4 Shirt Blue 400 Your query should now return the following:
Color allcolorsales
Red 500
Green 1300
Blue 1300
Note that, according to my observations, what is relevant is the columns order when the table was created. Re-ordering the columns afterwards does not seem to change the results of the query. This is, in my opinion, a bug.
7 Replies
When Marco Russo wrote his article, the value returned (500) was:
Total of Amount for all rows with
Product = "Bike" and Quantity = 1 and Amount = 100
or
Product = "Shirt" and Quantity = 2 and Amount = 200
1200 is the total of Amount rows for all rows where Product = "Shirt".
The implicit filter seems to ignore the columns Quantity and Price.When Marco Russo wrote his article, the value returned (500) was:
Total of Amount for all rows with
Product = "Bike" and Quantity = 1 and Amount = 100
or
Product = "Shirt" and Quantity = 2 and Amount = 200
1200 is the total of Amount rows for all rows where Product = "Shirt".
The implicit filter seems to ignore the columns Quantity and Price.Do you use Excel 2016?
yes,Laurent, I did it in excel2016 and also Power BI, the answer is equal.
Does different version of the tool make different answer?
Well, the Power Pivot engine in Excel 2016 is not the same as the one used by Excel 2013 (when Marco Russo wrote his article).
I have made a few tests, and here are my conclusions: this is a bug.
The long version is the behaviour no longer works as Marco Russo explained in his article. There is still an implicit filter but only on the first column in the table (at the time of creation).
If you create a new table with columns listed in this order: Quantity, Product, Color, Amount, like this:
Quantity Product Color Amount 1 Shirt Red 100 2 Shirt Red 200 2 Shirt Green 200 3 Bike Green 300 3 Shirt Green 300 3 Bike Blue 300 4 Shirt Blue 400 Your query should now return the following:
Color allcolorsales
Red 500
Green 1300
Blue 1300
Note that, according to my observations, what is relevant is the columns order when the table was created. Re-ordering the columns afterwards does not seem to change the results of the query. This is, in my opinion, a bug.
