Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
phchu1226
Helper IV
Helper IV

PowerBI column control while drill up/down

Hello,

 

Do anyone know whether PowerBI have this kind of feature?

The matrix will show more columns while drill-down, just like the images below.

In the first dimension, PAP, it contains 'sales' and 'order' column only.

While drilling down, it show two more columns, which are 'Count of Size2' and ''Count of Size3'.

Or any other way/suggestion to achieve this?

Thank you so much in advance!😃

 

phchu1226_0-1623912620137.png

 

phchu1226_1-1623912669033.png

 

1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

@phchu1226 , I doubt that is possible  using standard matrix.

 

You can have somthing like this using bookmarks

https://radacad.com/bookmarks-and-buttons-making-power-bi-charts-even-more-interactive

View solution in original post

9 REPLIES 9
amitchandak
Super User
Super User

@phchu1226 , I doubt that is possible  using standard matrix.

 

You can have somthing like this using bookmarks

https://radacad.com/bookmarks-and-buttons-making-power-bi-charts-even-more-interactive

selimovd
Super User
Super User

Hey @phchu1226 ,

 

yes that is possible. You can do drill down on rows and also on columns.

For columns just do it like for rows, add a hierarchy and then you will have the option to drill down there as well. Take a look here:

selimovd_0-1623915563037.png

 

Also check out the whole article:

Create a matrix visual in Power BI - Power BI | Microsoft Docs

 

If you need any help please let me know.
If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍
 
Best regards
Denis
 

@selimovd 

Hi, maybe little confused if I use 'Count of Size 1' and 'Count of Size 2' as the example.

 

what I want to do:

1: no need size1 and size2 in this dimension, and dont display size1 and size2 column, as it is meaningless to show the sum or average of size in this level

phchu1226_2-1624238260606.png

that is, just need sales in this dimension.

phchu1226_4-1624238412738.png

 

2. when drilled down, show the size1 and size2 for each item.

phchu1226_3-1624238334107.png

 

Is it possible to do this?🤔

 

 



HI @phchu1226,

According to your discretion, it seems like you want to show matrix column value on row, right?
If that is the case, you can take a look at the following blog which mentions 'show value on row feature in the matrix' if helps:

Power BI Desktop August Feature Summary | Microsoft Power BI Blog | Microsoft Power BI

Regards,

Xiaoxin Sheng

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

Hello, @v-shex-msft 

you want to show matrix column value on row => I guess no...?

From the screencap above, the row level is fine and it is what I expected. The dimensions are HC > FLM > FLMXX > FLMXX-XX-XX-XX

I just wanna know it is possible to show the data of 'Size' in FLMXX-XX-XX-XX level only but not in the higher level which are HC,FLM,FLMXX as the aggregate value of size is meaningless..

phchu1226_1-1624432091526.png

And that's why I dont want to show these columns (Size1 and Size2) until I drilled down to FLMXX-XX-XX-XX level, and is it possible to do so?

Thank you.

Hi @phchu1226,

Ok, it sounds like you want to control the visibility of the subtotal levels.

For this scenario, you can switch to the 'format' tab(paint roller icon) and find out the 'subtotals' property and turn on the 'per row level' option to accurately control the summary of each category group. (turn off subtotal of other fields except the top-level field)

Regards,
Xiaoxin Sheng

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

@v-shex-msft 

Hello,

if I turn off the subtotal, the subtotal of other columns will disappear..

I want to show the subtotal except the first two column.. 

phchu1226_0-1624605482443.png

 

Hi @phchu1226,

Did these fields generated by row and column field analyze or directly use multiple value fields on matrix value fields?

If you mean the first one, you can write a measure formula to replace the value field and add an if statement to skip the specific category value handling.

Clever Hierarchy Handling in DAX - SQLBI

If you mean the second one, you may need to create a parameter table with all of these value field names, then you can use the new field on the column feild and write a measure formula with a switch function to lookup correspond calculation results.

My Favorite DAX Feature: SELECTEDVALUE with SWITCH | Winston-Salem Power BI User Group (pbiusergroup...
Regards,

Xiaoxin Sheng

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

@selimovd 

Hello,

 

I know that there also is the drill down in columns.

My own thought is that I hope I can drill down in rows together with the column at the same time.

Is there any feature of PowerBI can achieve this?

 

 

For example, when I drill down the first dimension 'PAP', it will also drill down in column, that is, two more columns appear 'Count of Size1' and 'Count of Size2' after drill down to '1LDGCNF-250-25.5-36.75'.

 

In my case, PAP do not have any data about size2 and size3 as PAP is just a group, display size2 and size3 in this dimension is meaningless. The data of size2 and size3 need to be found in the second dimension (i.e. '1LDGCNF-250-25.5-36.75', so I hope that I can just show the data of size in the second dimension instead of the first dimension.

phchu1226_1-1624007654624.png

 

 

 

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.