Drill Through From Matrix Grand Total

Power BI
1

Hi
I have a matrix which is currently displaying sales data and I have drill through working from values within the matrix to a detail page. So, when I right click on a value in any column in the matrix, I get the ‘Drill through’ option appear in the popup menu from where I can select the required detail report - all works fine.

However, this doesn’t work when I right click on a grand total value at the very bottom of the matrix. I just get ‘Show as a table’ and ‘Copy’ options.

Is there a way of getting drill through to work from the grand total? I have a requirement to show the detail report for ALL data that makes up the report total.

Thanks

2

Hi @t.irani

Welcome to the community.

As far as I know, it’s a SSAS MDX limitation on how the mdx is structured. When you perform a drill-down action, a query is sent to the data model, this query is expressed in Multi-Dimensional Expressions (MDX). Since the MDX language does not support a drill-through command against a “Set”, thus you can not drill through for Grand Total.

If you would please upload the pbix file, maybe some of our experts would be able to help you.

Thanks and regards

Najah

3

Thanks Najah.

I’m not using SSAS, this purely within a Power BI data model. Also, the drill through works on all values and subtotals - just not the grand total.

I can’t upload this pbix as it has confidential data within it. I’ll try & create a pbix based on demo data that demonstrates the issue.

Thanks
Tiran

4

Hi

I’ve created a simple pbix that demonstrates the question.

And here’s the effect I’m seeing.

image
image609×730 46.1 KB

Thanks in advance for any further comments on this.

Tiran

Matrix GT Drill Through Demo.pbix (407.6 KB)

5

Hi
Thank you for uploading the file. I am wondering that if we look at it as a table, we are already drilling through the report and can see whatever is contributing in the total, maybe that’s why there is no way to drill through the Grand Total?
Regards
Najah

6

Hi

My aim is to be able to drill through to the transaction detail - not just the two columns on the main table - which works for every other row in the matrix. Drilling through to the detail page shows each transaction like so.

image
image1250×382 40.1 KB

Perhaps this is just a limitation of the matrix visualization right now?

I’ll try & find an alternative to this.

Thanks again for your comments.

Tiran

7

Hi
Thanks for the explanation and uploading the pbix Tiran. As per my knowledge, unfortunately it is limited at the moment. So it will drill through everything except the grand totals.
I hope that you would be able to come up with a solution, if you do, please share with us here so we can learn too.
Wish you the best.
Regards
Najah

8

Hi @t.irani , a response on this post has been tagged as “Solution”. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the check box. Thanks!