Forum Discussion
Repeating rows on drill down in table
Hi
I am trying to calculate the avr country price and compare it with the avr BU price when I drill down in a table I get all the countries shown when I apply a filter on BU. (country is managed in a separate table.
Attached is the formula I used and a copy of the result table.
What is it that I am not considering?
Hi JHF,
I notice that you use the ALLEXCEPT() in the measure. As this function will remove all context filters in the table except filters that have been applied to the specified columns. The measure will always return [AvrPrice 2015_BU Adj] values group by [BU Key], ignore which level drill down or drill up. Please try to modify the measure to below, the result can be evaluated on each drill down level.
Measure= CALCULATE([AvrPrice 2015_BU Adj], FILTER(Data,'Data'[BU Key]<=MAX('Data'[BU Key])))
Best Regards,
Qiuyun Yu
2 Replies
Hi JHF,
I notice that you use the ALLEXCEPT() in the measure. As this function will remove all context filters in the table except filters that have been applied to the specified columns. The measure will always return [AvrPrice 2015_BU Adj] values group by [BU Key], ignore which level drill down or drill up. Please try to modify the measure to below, the result can be evaluated on each drill down level.
Measure= CALCULATE([AvrPrice 2015_BU Adj], FILTER(Data,'Data'[BU Key]<=MAX('Data'[BU Key])))
Best Regards,
Qiuyun YuThanks...it worked when I made a minor tweek, adding the allselected to the formula.
ALCULATE([AvrPrice 2015_BU Adj];FILTER(ALLSELECTED(Data);Data[BU Key]=MAX(Data[BU Key])))