Sum of Average aggregations

[rtwheelerParticipant Started: 5Replies: 16Total: 21]

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.

CALCULATE(AVERAGE(Soh_Table[Soh Snapshot]),VALUES(Soh_Table[Product_no]))

In short I need to sum of all my individual products’ averages by site at any level.

Please see attached.

 

Attachments:

You must be logged in to view attached files.

[tomallanKeymaster Started: 0Replies: 417Total: 417]

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.

Attachments:

You must be logged in to view attached files.

[rtwheelerParticipant Started: 5Replies: 16Total: 21]

Hi Tom

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 ?

Regards

[tomallanKeymaster Started: 0Replies: 417Total: 417]

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:

Rob Collie’s “Comprehensive Power Pivot Course” and Russo/Ferrari’s “Mastering DAX”

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!

Tom

Attachments:

You must be logged in to view attached files.

[rtwheelerParticipant Started: 5Replies: 16Total: 21]

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”

Regards

[tomallanKeymaster Started: 0Replies: 417Total: 417]

An interesting showing how to apply DAX in different business scenarios can be found at this link, complete with workbook samples and detailed instructions.

A book was published with the same information, which you could also buy separately.

[Author]

Posts