Forum Discussion
average from table at higher granularity
I've seen other similar posts but nothing has really gotten me the result I need. This is an SSAS data source. I have a table, ItemDetail, that has cost records broken down to the Branch, Item, Location, Lot granularity. I want to get the average cost at a higher granularity, just by Branch and Item. So, based on my table below, Item 5529 would have an average measure = 12.67, and 6200 = 6. Branch - Item - Location - Lot are not necessarily unique, as I can have multiple records for a single combination, like Branch A records below.
Anonymous
In the example in the original post, it seems you are looking to get average cost just by Item (12.67 and 6). It is not necessary to filter the brand column in filter expression.Measure = CALCULATE(AVERAGE([Unit Cost]),FILTER(ALLSELECTED('Table (2)'),[Item]=MAX([Item])))Paul Zheng _ Community Support Team
If this post helps, please Accept it as the solution to help the other members find it more quickly.
3 Replies
Anonymous , Try
a Measure
AverageX(filter(Allselected(Table), Table[Branch] =max( Table[Branch]) && Table[Item] =max( Table[Item])),Table[UnitCost])
or a column
AverageX(filter(Table, Table[Branch] =earlier( Table[Branch]) && Table[Item] =earlier( Table[Item])),Table[UnitCost])That seems to break it out rather than aggregating the sum. Here's what I have before:
When I add the suggested measure, it does this:
AmountUnitCost is a column on the table (underlying transaction table), so rows are being grouped by it in PBI, until that measure makes it wonky (2nd screenshot).
Anonymous
In the example in the original post, it seems you are looking to get average cost just by Item (12.67 and 6). It is not necessary to filter the brand column in filter expression.Measure = CALCULATE(AVERAGE([Unit Cost]),FILTER(ALLSELECTED('Table (2)'),[Item]=MAX([Item])))Paul Zheng _ Community Support Team
If this post helps, please Accept it as the solution to help the other members find it more quickly.
