How does aggregation table get populated?

[Anonymous]

Hello, I am trying to figure out aggregation tables and apparently missing something obvious... I created a super-simple model with 2 tables, orderlines and product. I would like to test grouping income by product name, so I created a new table using "Enter data" button in PowerQuery and created a columns for Sum and Product. When I configure aggregation for the new table and select to sum by amount from orderlines and group by product name from product I don't get any data in that table automatically as I would expect. I thought that Power BI will automatically fill the table based on aggregation configuration I did, but that is apparently not the case. Guides I was able to find online usually start with completed data model and I was not able to find any which start from the scratch and see how the aggregation table is created.

Thanks in advance 🙂

[v-cazheng-msft]

Hi @Anonymous 

If you want to understand the aggregation in Power BI, you can take a look at these articles Understanding Power BI Aggregations  and Use aggregations in Power BI.

For creating aggregation table in Power BI, you can refer Power BI Aggregation: Step 1 Create the Aggregated Table.

Hope it can helps!

Best Regards

Caiyun Zheng

Is that the answer you're looking for? If this post helps, then please consider Accept it as the solution. Really appreciate!

[Anonymous]

Hi, your first link is totaly irrelevant since it explains another type of aggregations, not aggregation tables.

The other link is from Microsoft documentation which I have ofcourse read before I posted here, but this one jumps over the step how the agg table is populated. It misleads me (maybe) in direction for me to think that Power BI should auto-populate the table when I configure gorupings and summarization.

Article from third link describes copying the fact table and then grouping on it, without possibility to use another dimension table, as you can see in screenshots, configuration of the grouping does not give an option to choose where the source column is comming from, it is limited to the current table.

I've been trying to get the answer on this question a long time now in all possible channels, is it possible that there is noone out there who can answer: Should Power BI populate the table, which is configured as aggregation table, automatically, or am I responsible to get the data into the table?

Thanks in advance

[techycraw]

I tried applying the method when I was learning my Power BI Course

After you create the new table that has the fields you want, in the Fields pane of any Power BI Desktop view, right-click the table, and select Manage aggregations. The Manage aggregations dialog shows a row for each column in the table, where you can specify the aggregation behavior.

[Anonymous]

Sure, and what happens then? Should Power BI automatically populate the table from data in direct query tables? Or am I responsible for getting the data?

[amitchandak]

@Anonymous ,The information you have provided is not making the problem clear to me. Can you please explain with an example.
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.
Appreciate your Kudos.

[Anonymous]

Not so easy for me to share the data. But it is quite simple, I have 2 tables which use DirectQuery storage mode, orderline and product. Orderline table contains productid and totalamount. Product table contains productid and name columns. A relationship exists on productid. I want to create an aggregation table which contains Sum and Product name columns and create a simple visual which shows sum per product. Should PowerBI automatically populate my aggregation table or not? When I configure my aggregation table (which is created manually by using "Enter data" in powerquery window) and click "Apply all", the table is hidden as it should be, but no data gets into the table automatically.

[amitchandak]

@Anonymous ,You will have that in visual. No need of new tbale.  if you drag product name from product and totalamount from order it will by deafult the aggregation on number is sum, else right click on the column (should drag in values in case of matix) and change aggregation .

or create a measure

totalamount m = sum(Order[totalamount])

and use that.

[Anonymous]

@amitchandak , I know I don't really need aggregations table in this simple scenario, but I am in learning process and trying to understand how aggregations work. Suppose that my orderline table contains millions of rows and that I want to group by not only the product, but region, customer, day of the week... I am trying to make a simple as possible example to see how it works and test it.