Forum Discussion

JHF's avatar
JHF
Advocate I
9 years ago
Solved

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 ...
Need Help
  • v-qiuyu-msft's avatar
    v-qiuyu-msft
    9 years ago

    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

     

     

     

v-qiuyu-msft's avatar
v-qiuyu-msft
Community Support
9 years ago

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

 

 

 

JHF's avatar
JHF
Advocate I
9 years ago

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