Forum Discussion
GroupBY and summarize
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.
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.
Hello, Ok
As they are based on calculation ' SUM, MIN, MAX, ... ' which produce a numerical columns I was expecting them to be summurized in Desktop. I understand now that Group By prevents further summarization. I wll close the ticket but I have noticed that, depending on the case, these numerical columns can be summarized and recognised as numerics !
To be sure, we tested on the same file with a colleague, at the end he got numeric output that could be summarized but not me !
Strange
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.