Forum Discussion
Drill down into different data
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
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
1 Reply
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