cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
J_R_Cribb
Helper I
Helper I

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
v-shex-msft
Community Support
Community Support

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
If this post helps, please consider accept as solution to help other members find it more quickly.

View solution in original post

1 REPLY 1
v-shex-msft
Community Support
Community Support

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
If this post helps, please consider accept as solution to help other members find it more quickly.

View solution in original post

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

R2 (Green) 768 x 460px.png

Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.

Top Solution Authors
Top Kudoed Authors