Forum Discussion
Group BY
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
Power Query transforms and Power BI DAX calculated columns are immutable - they are only calculated once during the initial load and will not react to end users interacting with the filters on your report. Most likely you are looking to create a measure that does the grouping for you (there are lots of DAX functions for that).
Hi MissEllaZ ,
Yes, this is expected behavior in Power BI. When you use the Group By feature in Power Query, the aggregations are performed during data load, and the results are stored as regular columns in the model rather than dynamic measures. Because of this, Power BI treats them as standard fields, not as measures, which is why you’re unable to change their aggregation or use them flexibly in visuals without creating new DAX measures. Changing the data type doesn’t affect this behavior since the limitation is due to how the data is modeled, not its type. If you need dynamic calculations such as averages, sums, or filter responsive metrics, you’ll need to create measures in DAX and ideally keep your data at a more granular level instead of fully aggregating it in Power Query.
Keep data at granular level and create measures in DAX like:
Total Sales = SUM(Table[Sales])
Avg Sales = AVERAGE(Table[Sales])
Hope this helps.
Thank you
5 Replies
Hi MissEllaZ ,
Yes, this is expected behavior in Power BI. When you use the Group By feature in Power Query, the aggregations are performed during data load, and the results are stored as regular columns in the model rather than dynamic measures. Because of this, Power BI treats them as standard fields, not as measures, which is why you’re unable to change their aggregation or use them flexibly in visuals without creating new DAX measures. Changing the data type doesn’t affect this behavior since the limitation is due to how the data is modeled, not its type. If you need dynamic calculations such as averages, sums, or filter responsive metrics, you’ll need to create measures in DAX and ideally keep your data at a more granular level instead of fully aggregating it in Power Query.
Keep data at granular level and create measures in DAX like:
Total Sales = SUM(Table[Sales])
Avg Sales = AVERAGE(Table[Sales])
Hope this helps.
Thank youPower Query transforms and Power BI DAX calculated columns are immutable - they are only calculated once during the initial load and will not react to end users interacting with the filters on your report. Most likely you are looking to create a measure that does the grouping for you (there are lots of DAX functions for that).
Hi,
Share some data to work with. Share it in a format that can be pasted in an MS Excel file. Show the expected result as well.
Hi MissEllaZ ,
We’d like to follow up regarding the recent concern. Kindly confirm whether the issue has been resolved, or if further assistance is still required. We are available to support you and are committed to helping you reach a resolution.
Thank you.Hi MissEllaZ ,
May I ask if you have resolved this issue? Please let us know if you have any further issues, we are happy to help.
Thank you.
