Forum Discussion
Helper IOnly show selected values at drill down?
Hello All,
I am trying to limit the values shown when drilling down in a matrix to only show a category subtotal where there is no item-specific data, and show the item specific data where applicable.
The Image below shows on the left hand side what I am currently seeing, and on the right is what I would like to achieve. Please also see below sample data .pbix.
Sample Power BI Report with Data as above
Thank you in advance!
Hi J_R_Cribb,
Please follow below steps to achieve your requirement.
1. Modify the 'cross filter direction' option to 'both'.
2. Write a measure to add some filters on the calculation of the budget column.
Total = VAR current_type = LASTNONBLANK ( 'Customer Type'[Cust Type], [Cust Type] ) RETURN IF ( COUNTX ( FILTER ( ALLSELECTED ( 'Customer Type' ), [Cust Type] = current_type ), [ID] ) <> 1 || COUNTROWS ( 'Customer Name' ) = COUNTROWS ( FILTER ( ALLSELECTED ( 'Customer Name' ), [ID] = MAX ( [ID] ) ) ), SUM ( Budget[Budget] ) )3. Remove the ID column and turn on the total feature of customer name.
Result:
Notice: I attach the pbix file below.
Regards,
Xiaoxin Sheng
1 Reply
Hi J_R_Cribb,
Please follow below steps to achieve your requirement.
1. Modify the 'cross filter direction' option to 'both'.
2. Write a measure to add some filters on the calculation of the budget column.
Total = VAR current_type = LASTNONBLANK ( 'Customer Type'[Cust Type], [Cust Type] ) RETURN IF ( COUNTX ( FILTER ( ALLSELECTED ( 'Customer Type' ), [Cust Type] = current_type ), [ID] ) <> 1 || COUNTROWS ( 'Customer Name' ) = COUNTROWS ( FILTER ( ALLSELECTED ( 'Customer Name' ), [ID] = MAX ( [ID] ) ) ), SUM ( Budget[Budget] ) )3. Remove the ID column and turn on the total feature of customer name.
Result:
Notice: I attach the pbix file below.
Regards,
Xiaoxin Sheng