Forum Discussion
GroupBY and summarize
Hi,
I have a dataset
Based on which I used a Group By in power query to generate my metrics (7 metrics) aggregated by three columns
When I go to visualisations I am not able to display them as metrics or measures or to change the their displays. They show as dimensions, if I want to have the average I needed to create a new measure
I want to ensure whether this is a normal behavior of PowerBi. The idea was to simplify the aggregation without using Dax but by using Group By featurzs and now I am not able to perform more calculations.
FYI i changes the data typz to decimal then to whole number but still not working as expected
Thank you
Hi MlleEllaZ00,
The behavior you’re noticing is caused by how the column is defined in the model, not just the Group By function. In Power BI, numeric fields are automatically aggregated, but this depends on the column’s data type and default summarization setting. If your column is set as text or to Don’t summarize, it acts as a dimension and won’t aggregate, which is why your colleague can summarize it but you can’t. This can happen even if you’re using the same file because of model-level settings. Make sure the column data type is numeric and the default summarization is set to options like Sum or Average.
Work with aggregates (sum, average, and so on) in Power BI - Power BI | Microsoft Learn
Best practices to optimize Q&A - Power BI | Microsoft Learn
Thank you.
9 Replies
HI MlleEllaZ00,
Have you had a chance to review the solution we shared by danextian alish_b Kedar_Pande ? If the issue persists, feel free to reply so we can help further.
Thank you.
Yes, normal behavior. Group By creates calculated columns (dimensions), not measures.
Fix: Right-click aggregated column → Create measure → use in visuals for SUM/AVERAGE formatting.
Power Query Group By = static aggregation table. DAX measures = dynamic calculations.
Thank you Kader,
but where can I find : Right-click aggregated column → Create measure → use in visuals for SUM/AVERAGE formatting.
Powerquery or PowerBI (visualisation part) I am quite new 🙂
Hi MlleEllaZ00
Using Group By in Power Query creates a summarized table based on the columns you choose to group by. The grouped columns are preserved, while new columns are added according to the selected aggregations (such as minimum, maximum, average, or count). However, because the data is aggregated, detailed information from the original dataset is lost. So if you want the detailed information do not group.
Hi MlleEllaZ00 ,
How does your data looks like once you have completed the Group By operation? Can you share a sample of the data structure (without actual data, just representative data)? I am asking this to see if there is something Pivot/Unpivot related that could help.
Hello, I don't have a pivot
I start from :
Dimension 1, Dimension 2, Dimension .. 5, Date 1, Date 2, Date 3
Then :
1- I create Date Diff
Dimension 1, Dimension 2, Dimension .. 5, DateDif (Date1, Date 2), Date Diff(Date 2, Date 3), Date Diff ( Date 1, Date 3)
2- I keep only Dimension I need and Group By
Dimension 1, Dimension 2, Max Date Diff 1, Max DateDiff 2, Max DateDIff 3
After that, I expect my Date IDff to be metrics, I an aggregate in PowerBI
But they are not recognised as such in PowerQuery or PowerBI either
Thanks
HI MlleEllaZ00,
When you use the Group By operation in Power Query, it produces a summarized table with pre-aggregated column values like Max or Sum. These fields are considered regular columns in Power BI, not dynamic measures. To allow flexible aggregation based on report filters and visuals, you need to create measures separately in the model using DAX.
How to GROUP BY or summarize rows - Power Query | Microsoft Learn
Thank you.
