Forum Discussion
What determines which numeric fields get Aggregates?
Interesting. Just to keep the conversation alive, I observe the following.
Load the text file - I see that price and cost default to "Sum"
Add another (fact) table and a relationship is automatically determined between the 2 tables (BarID)
The aggregates of Price and Cost automatically changed to "Do Not Summarize"
So, based on my testing I would say that initial loads of all data default to an aggregate, unless a relationship is detected/applied that will drive a 1 to many relationship, and thus change the default behavoir based on a fact/dimension relationship...
It would be good to hear an official answer, but that is what I'm seeing.
The sources is a simple text file.
"BarID","Flavor","Size","Price","Cost"
1,"Milk Chocolate","Small","1","0.4"
2,"Dark Chocolate","Small","1","0.4"
3,"Caramel","Small","1","0.4"
4,"Crisp","Small","1","0.4"
5,"Milk Chocolate","Large","2","0.8"
6,"Dark Chocolate","Large","2","0.8"
7,"Caramel","Large","2","0.8"
8,"Crisp","Large","2","0.8"
Interesting. Just to keep the conversation alive, I observe the following.
Load the text file - I see that price and cost default to "Sum"
Add another (fact) table and a relationship is automatically determined between the 2 tables (BarID)
The aggregates of Price and Cost automatically changed to "Do Not Summarize"
So, based on my testing I would say that initial loads of all data default to an aggregate, unless a relationship is detected/applied that will drive a 1 to many relationship, and thus change the default behavoir based on a fact/dimension relationship...
It would be good to hear an official answer, but that is what I'm seeing.
Seem like a decent theory. Here is my query for my Bar table, in case you want to play.
let
Source = Csv.Document(File.Contents("C:\Users\Mark\OneDrive\Presentations\ClickClickWOW\Chocolate Sales\Bar.txt"),[Delimiter=",",Encoding=1252]),
#"Promoted Headers" = Table.PromoteHeaders(Source),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"BarID", Int64.Type}, {"Flavor", type text}, {"Size", type text}, {"Price", Int64.Type}, {"Cost", type number}}),
#"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"BarID", "Bar ID"}}),
#"Added Custom" = Table.AddColumn(#"Renamed Columns", "Profit", each [Price]-[Cost]),
#"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"Profit", type number}})
in
#"Changed Type1"