cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
J_R_Cribb Regular Visitor
Regular Visitor

Only 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.JPG

 

Sample Power BI Report with Data as above

 

 

 

Thank you in advance!

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Only show selected values at drill down?

Hi @J_R_Cribb,

 

Please follow below steps to achieve your requirement.

 

1. Modify the 'cross filter direction' option to 'both'.

4.PNG

 

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.

5.PNG

 

Result:

6.PNG

 

Notice: I attach the pbix file below.

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.



For learning resources/Release notes, please visit: | |
1 REPLY 1
Community Support Team
Community Support Team

Re: Only show selected values at drill down?

Hi @J_R_Cribb,

 

Please follow below steps to achieve your requirement.

 

1. Modify the 'cross filter direction' option to 'both'.

4.PNG

 

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.

5.PNG

 

Result:

6.PNG

 

Notice: I attach the pbix file below.

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.



For learning resources/Release notes, please visit: | |