Forum Discussion

ryan-gao's avatar
ryan-gao
Helper III
9 years ago
Solved

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, t...
  • LaurentCouartou's avatar
    LaurentCouartou
    9 years ago

    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:

     

    QuantityProductColorAmount
    1ShirtRed100
    2ShirtRed200
    2ShirtGreen200
    3BikeGreen300
    3ShirtGreen300
    3BikeBlue300
    4ShirtBlue400

     

    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.

     

LaurentCouartou's avatar
LaurentCouartou
Solution Supplier
9 years ago
Hi Alberto.

First, let me apologize for attributing the article to Marco.

The behavior you describe is indeed a feature. What I was referring to as a bug, is the fact that the result of the query changes depending on the order of the columns in the table, when the table was created. This buggy behavior cannot be seen in the simplified example you just provided, because it only has 2 columns.

To clarify my point: Only the leftmost column will be added to the shaped set. Furthermore, which column is the leftmost one is set when the table is added to the model. Reordering columns will not modify the results afterwards.
LaurentCouartou's avatar
LaurentCouartou
Solution Supplier
9 years ago

Here are the steps to reproduce the bug, in Power BI Desktop

 

Create a new PBI file

Add a new query and call it Sales

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcsrMTlXSUQpKTQGSTkqxOtFKwRmZRSU4xNyLUlPz4KJQzTBBR8JKnXJKU+EqMcRiAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Product = _t, Color = _t, Model = _t]),
    #"Type modifié" = Table.TransformColumnTypes(Source,{{"Product", type text}, {"Color", type text}, {"Model", type text}})
in
    #"Type modifié"

Add a new query and call it Sales2

let
    Source = Sales,
    #"Colonnes triées" = Table.ReorderColumns(Source,{"Model", "Product", "Color"})
in #"Colonnes triées"

The second query only reorders the columns from the first one.

Now, back to the model, we should have two tables : Sales and Sales2.

 

Let us add a two calculated tables: TestSales and TestSales2.

TestSales = SUMMARIZE ( Sales,
    Sales[Color],
    "Sales", COUNTROWS( Sales ),
    "AllColorSales", 
    CALCULATE (
        COUNTROWS ( Sales ), 
        ALL ( Sales[Color] )
     ),
    "AllSales", 
    CALCULATE (
        COUNTROWS ( Sales ),  
        ALL ( Sales )
    )
)

TestSales2 = SUMMARIZE ( Sales2, Sales2[Color], "Sales2", COUNTROWS( Sales2 ), "AllColorSales2", CALCULATE ( COUNTROWS ( Sales2 ), ALL ( Sales2[Color] ) ), "AllSales2", CALCULATE ( COUNTROWS ( Sales2 ), ALL ( Sales2 ) ) )

 

Both calculated tables should return the same result. (left: TestSales, right: TestSales2)

 

 

 

 

 

 

 

jk