I am working with data at local authority level and want to be able to refer to aggregate measures for the region in which each local authority sits. A method that works is to run a Query that Groups by say Region and date, and then to Merge that Query with the original one using the region and date fields in both queries to produce an expanded table that includes the corresponding regional aggregate alongside each local authority entry.
But is this the most efficient way to produce such an expanded table, and does the answer depend upon how large the underlying dataset is? Currently, I am working with millions of records (on a mid-range laptop) and run times are quite slow.
Thanks. Bob
Hi Bob,
I'd say this is probably a job for Power Pivot, but it's difficult to say for certain without seeing your data structure and the way you want to visualise and interact with it.
With the Power Query approach you are adding data to your model with these calculations, whereas with Power Pivot this data can be calculated in a PivotTable and therefore doesn't result in millions of extra records.
Mynda
Thanks Mynda
I attach a simplified example table of the sort I am trying to work with. Eventually, I want to be able to build a Data Model that allows me to show data for the number of transactions in Brent alongside comparable data for (in this case) Outer London, and generally to be able to associate Outer London data with that for Brent and transform/manipulate as if it was Brent data.
This link - https://commonslibrary.parliament.uk/social-policy/housing/home-ownership/constituency-data-house-prices/ - gives an idea of the functionality I am trying to create. It delivers much of the functionality shown by creating an expanded data table that features the corresponding data for region and country alongside the parliamentary constituency.
It works, but would Power Pivot provide a more efficient process?
Bob
Hi Bob,
Thanks for sharing the examples. Seeing that the Commons Library is a Power BI dashboard confirms my original idea that this is a job for Power Pivot. Remember Power BI also uses Power Pivot to model the data.
It's difficult to say for certain, but I expect their model has dimension tables that map the constituencies to regions which enables the plotting of the data at constituency, region and England & Wales levels of detail.
See example attached.
Mynda
Hi Mynda
Please could you provide an idea of what the DAX function would look like to generate the aggregate columns. I've played around with SUMX, CALCULATE, FILTER and RELATED but nothing seems to work.
Thanks
Bob
Hi Bob,
In the file I attached in my previous reply there are aggregate column measures. What did you want that's different to those already in that file?
Mynda
Sorry Mynda, I had not looked properly at the Power Pivot table in which you had defined the measures. All I have to do now is to get to grips with the AllExcept function - are there other helpful guides to how/when to deploy AllExcept?
Thanks again.
Bob
In session 9.07 of the Power Pivot course I cover ALLEXCEPT.
