Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe 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.
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
Hi @Anonymous 🙂
I'm afraid I might not get the total extension of your requirement, but is this what you need?
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
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:
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!
User | Count |
---|---|
120 | |
69 | |
68 | |
57 | |
50 |
User | Count |
---|---|
166 | |
82 | |
68 | |
65 | |
54 |