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

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
Anonymous
Not applicable

Drill down and drill through

Hello, I am working on a DB with numerical values for Real Estate, Improvements, utilities, Etc, all adding up to a gross value. These expenses are also broken down by district. I have a card visual with the sum [GROSS] for all districts =(Total Gross). I was trying to drillthrough on this card visual to get to a details page, but I can't get it to work. 

 

Furthermore, in my details page, I have created a pie chart with a breakdown of the Total GROSS (by Real Estate, Improvements, Utilities, Etc). I was trying to drill down and see the break down of each component (Real Estate, Improvement, Etc) by district. Once again, I can't seem to figure this out. I have tried changing the visuals for both, creating hierarchies and measurements, nothing has worked so far. 

 

Please find the Google Drive where the files are stored below.  Thank you very much in advance!

 

https://drive.google.com/drive/folders/1a1DXTHjlQo2Tw5HIrQkRrhkkbEns6EZO?usp=sharing

 

1 REPLY 1
JoaoMarcelino
Responsive Resident
Responsive Resident

Hi @Anonymous 🙂

I'm afraid I might not get the total extension of your requirement, but is this what you need?

example.gif

If so, what you need to do is the following:
1- Convert you implicit measure (auto sum) in an explicit measure, by creating:

 

 

Total Gross = sum (Elementary[GROSS])

 

 

2- On the card, replace the previous field by the new measure.
3- Go to the detail page and on the Drill through option, drag and drop that measure

JoaoMarcelino_0-1722508502903.png

4- Now, when you go to the 1st page and right-click on the card (or any other visual that uses that measure) will allow the drillthrough to the detail page.

Note: This is a fix to your need, but it isn't what we can call a "good practice". Keep in mind that drill through usually is used to allow the navigation to a detail page, in the context of a certain dimension, e.g,  Country, Store, Color, etc... if we drill through based on a measure it works pretty much like a "shortcut" our navigation button to other page that uses that measure.

Now, regarding your other question, this is harder to explain and takes longer, by I'll try to at least give you a reference: for you to get that level of detail, e.g, decompose gross by category Real estate, improvements, personal, etc, you need to either create a measure that is a switch betweem the sum of those several categories, and the, or a field parameter that switches between the sum of each column....
other approach that i would advise is: don't think of Power BI the same way you think about excel; if the gross is the sum of real estate, improvements, personal utilities, etc, go to power query and unpivot the columns to get something like:

JoaoMarcelino_1-1722510158372.png


Create a new blank query in Power Query, clean the code and copy paste this:

 

let
    Source = Excel.Workbook(File.Contents("C:\Users\ximena.bustamante\OneDrive - Maricopa County\Abstracts\Data\20230710\Abstract County  20130710_San.xlsx"), null, true),
    Elementary_Sheet = Source{[Item="Elementary",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Elementary_Sheet, [PromoteAllScalars=true]),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Promoted Headers", {"ELEMENTARY SCHOOL DISTRICTS"}, "Attribute", "Value")
in
    #"Unpivoted Columns"

 



By doing so, you only have to have a measure which is the sum of column Value (you can rename it), and then you can play with dax measures and filter contexts. Also, you can use the column with attribute às legend or any other strategy that allows you to play with your data and get the intended result.
Regarding the other columns you've calculated in Power Query, you can now calculate the measure in dax for that by playing with filters in the calculate expression: CALCULATE – DAX Guide

Hope I was of assistance!
Cheers,
Joao Marcelino
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
Ps- Meet me on linkedin!

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

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