Forum Discussion
How does aggregation table get populated?
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 🙂
8 Replies
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.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.
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.
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.
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!
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
