Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

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, Loca...
Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    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.
amitchandak's avatar
amitchandak
Super User
5 years ago

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])

Anonymous's avatar
Anonymous
Not applicable
5 years ago

amitchandak 

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).