Forum Discussion

Macdavel's avatar
Macdavel
New Member
9 years ago
Solved

Using measure aggregate in row level calculations

I would like to calculate a ratio on my table and then use the resulting ratio in other calculations at the row level (Calculated column). The measure aggregates correctly but  when I use it in a row level calculation, it recalculates the ratio for that particular row. Is there a way to force a measure to calculate at the table level aggregation at the row level while still keeping it responsive to page level filters (i.e without using ALL/ALLEXCEPT)?

Show and Tell
  • Anonymous's avatar
    Anonymous
    9 years ago

    The first thing i think needs to be asked:

    How do you expect a calculated column to take into account "Page Level Filters"?  Remember, calculated columns are calculated as part of the data refresh and are independant of Report, Page, and Visual level filters.

  • v-sihou-msft's avatar
    v-sihou-msft
    9 years ago

    Macdavel

     

    Anonymous is correct. The visual, page, report level filter just control the context we populated in visual. However, measure evalulates the calculation based on current context, which is row level in your scenario. It has nothing to do with visual, page, report level filters. To force a measure to calculate at the table level aggregation, you have to use ALL() function.

     

    Regards,

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    The first thing i think needs to be asked:

    How do you expect a calculated column to take into account "Page Level Filters"?  Remember, calculated columns are calculated as part of the data refresh and are independant of Report, Page, and Visual level filters.

  • v-sihou-msft's avatar
    v-sihou-msft
    Microsoft Employee
    9 years ago

    Macdavel

     

    Anonymous is correct. The visual, page, report level filter just control the context we populated in visual. However, measure evalulates the calculation based on current context, which is row level in your scenario. It has nothing to do with visual, page, report level filters. To force a measure to calculate at the table level aggregation, you have to use ALL() function.

     

    Regards,