Drill down into different data

[addd123]

Hello, 

Im trying not only switch the axis when drilling down, but also the values. 

i.e. start with:

Axis: Projects     Value: Total expenses

To

Axis: Employees     Value: Expense A,  Expense B, etc. 

I have found this:

ChangeInDrillDown = 
     VAR  AverageCalc = AVERAGE(TableName[Amount])
     VAR  SumCalc   = SUM(TableName[Amount])

RETURN
IF(ISFILTERED(TableName[DrillLevel]);SumCalc;AverageCalc)

However i cannot find the ChangeInDrillDown on the microsoft website and it gives an error in DAX when creating a measure. If it is correct, where do i input the code and how do i get the drill down button to respond to it?

Or is there another way to achieve the above?

Kind regards, 

Ad

[Anonymous · Accepted solution]

Hi @addd123,

I assume that you refer to this similar thread to write the above formula, right? If that is the case, firstly, there are syntax errors in the formula, please change it to the following formula, replace the columns(Table1[Amount]) and Table1[DrillLevel]) with your own fields.

ChangeInDrillDown =
     VAR  AverageCalc = AVERAGE(Table1[Amount])
     VAR  SumCalc   = SUM(Table1[Amount])
 
RETURN
IF(ISFILTERED(Table1[DrillLevel]),SumCalc,AverageCalc)

 Secondly, you can right click your table and choose “New Measure” to input the code.


Thirdly, create clustered column chart, drag your own fields into Axis and drag ChangeInDrillDown into Value. For more details, please check the example in the attached PBIX file.


Thanks,
Lydia Zhang