I would like to ask for some help with the need for aggregating (sum and count).
The same aggregation need occurs in two different data models. In the first situation I have one table containing all details and in the second situation the data is split over several tables.
First data model:
This is in fact a simplified example for a data model that in reality consists of 15.000 buildings, owned by 1.500 companies in many cities. The end user wants a table in the Power BI report which lists the companies, with the possibility to filter for a specific region, showing the number of buildings and the sum of their capacity for both the entire company and for the selected region. Like this example (filtered with City = New York):
Preferably displayed like this in the report visual:
This way of presenting requires a separate measure with some concatenation of the Company name with both calculations or perhaps it requires the use of a calculated column.
I managed to create a DAX measure for “No. of Buildings in selected area” and a DAX measure for SUM of Capacity in selected area. The problem for me lies in the Total no. of Buildings and the Total sum of Capacity and the integration of these values with the company name in a single field.
Besides the struggle with the DAX formulas, I am also wondering whether to perform these calculations through a DAX measure or to create these columns in Power Query. With regard to performance and given the fact that the Total no. of Buildings and the Total sum of Capacity will stay the same regardless what filtering takes place by the end user, I can imagine it’s worthwhile to calculate these columns in Power Query.
If so, I would like to extend my Building table in the query editor with two columns, like this:
I am aware of the fact that I create data redundancy here and it would be better to have a separate table holding all company details. But my data source holds only this one table and otherwise I first have to extract all unique companies in a separate table creating some other difficulties and performance issues.
So my questions are:
- Is it better to perform these calculations in Power Query or in DAX?
- How to work this out, either in Power Query or in DAX language?
Last but not least, I have a similar situation, but with a data model consisting of three separate tables:
Also in this situation I am aware of the imperfection of the data model. I could consider an append of the Building table + Capacity table, but in reality this capacity table holds almost a hundred different columns, with capacity data for many moments in the past and forecast for the future.
For this data model I face the same challenge of calculating the total number of buildings and sum of capacity for each company, regardless any filters.
Thanks for all useful suggestions!