GroupBY and summarize

[MlleEllaZ00]

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

[v-saisrao-msft]

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.

[alish_b]

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.

[MlleEllaZ00]

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

[v-saisrao-msft]

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.

[MlleEllaZ00]

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 

[v-saisrao-msft · Accepted solution]

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.

[danextian]

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.

[Kedar_Pande]

@MlleEllaZ00 

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.

[MlleEllaZ00]

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 🙂