November 8, 2016 at 4:38 am #6665
I would to know if it is possible to sum your average aggregations? (sum the result of one measure )
I have a table by site, product and week. I would like to take average across all the week for each individual product and sum their averages
I have tried the following formula: but its only averaging the sum the over number and not the individual average.
In short I need to sum of all my individual products’ averages by site at any level.
Please see attached.November 9, 2016 at 12:14 am #6679
From my point of view, single table models make it difficult to work with measures across multiple objects such as sites, products and weeks.
Please see attached workbook for some ideas using a multi-table model.
Hope this helps.November 9, 2016 at 4:30 am #6682
thanks for this,very interesting formula, I just realized how little I actually know about powerpivot functions and how to use them effectively, do you have any books you recommend?. For some reason I always tend to use a single table thinking my data accuracy would be allot better than normalized tables. I see the relatabletable is a function I never would of considered.
Would this formula be practical in a very large model? if not, is the link back tables then your second option ?
RegardsNovember 9, 2016 at 7:30 pm #6686
I think the formula would be practical over a very large multi-table model that takes advantage of relationships. Consider this: for a single table model, the Power Pivot engine will always do a full table scan for every filter on the pivot and within the measure, but a filter on a look up table column (because of the relationship between the look up table and the fact table), will only scan the related rows in the fact table.
A possible shortcoming of the formula I used for average will probably not be its speed, but that it assumes the number of weeks in the Weeks table only cover the weeks in the fact table. Please see the attached workbook that contrasts the original average measure with a revised formula that depends on weeks the product sold, instead of depending on the number of weeks in the Weeks table.
In the attached workbook only one row of data was modified: the snapshot of Product 2 for site M01 in week 1 was removed. The original formula divides by the number of weeks covering the report (the weeks table was created through Power Query against the original data set), but the revised formula is based on the number of weeks the product sold.
But which one is “right”? The answer could be either one (or neither): it depends on the requirements.
Three books that I recommend are:
Power Pivot and Power BI, by Rob Collie and Avi Singh
M is for (Data) Monkey, by Ken Puls and Miguel Escobar
Definitive Guide to DAX, by Marco Russo and Alberto Ferrari.
For learning about DAX, other than on-site training, there are only two courses I currently recommend:
To me the difference between the two is that Rob’s course focuses more on expanding reporting skills where the other focuses more on how DAX works. Both are of great value to me.
If you have found this topic helpful, please tell a friend about your experience in the PowerPivotPro forums!
TomNovember 10, 2016 at 5:48 am #6691
Ok great thanks for the reply, reason I asked if it would work over a very large table (85mil rows) is because I applied it to my model and it ran forever but on a smaller model it works better. Like you said I need to change my approach and work with more tables and not one big one. The only reason I was a against it is because I end up related columns into the main table, now I see the relatedtable function with will do the trick I quess.
I think I got the first book, which doesn’t really cover practical business cases, think I will have a look at the definite guide to DAX.
thanks allot for the feedback, I certainly will share my experience in our “BI community”
RegardsNovember 10, 2016 at 6:33 am #6692
The forum ‘Power Pivot’ is closed to new topics and replies.