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
CPESAdminII
New Member

Yearly Comparisons When Drilling Down

Hello,

 

I am building my second Power BI report and am trying to get a specific visual result when I drill down. I am not sure if it should be done with Measures or if there is a simpler way of achieving the results I am looking for.

 

I have created a SQL query and imported a table that has a date range and costs.

 

At the highest level of the chart I see a comparison of three different companies accross a span of three years.

 

When I drill down into the present year, I want to see cost data from the present year and the two previous years as well.

 

In essence I want to drill down into the chart and contiue to see the comparison by year for ever more granular data.

 

Is this possible?

 

 

1 ACCEPTED SOLUTION

Ok gotcha - you want to put the drill path in the axis and the years in the legend - that should do it.  You can adjust what shows up in the report in the filters section.

 
Screenshot.png
Austin is VP Operations at PowerPivotPro and a professional self-service enthusiast 🙂

View solution in original post

4 REPLIES 4
austinsense
Impactful Individual
Impactful Individual

Your explanation is a little confusing.

 

  • You want 1) to drill into the current year and 2) still see the previous two years - Those two things are incompatible with each other.
  • You're saying that you want to continue to see the YoY comparison as you drill to "ever more granular data" - What exactly are you drilling into?

 

I think your issue is probably very solvable.  Maybe try to explain it a little differently or show some screenshots.

Austin is VP Operations at PowerPivotPro and a professional self-service enthusiast 🙂

I don't quite believe it's possible, but I will try to clarify.

I want to start with the first view but be able to drill down to the second...

What I want to do is to be able to see the data on a yearly basis continuously as I delve deeper into the chart.

Let's say the first view is the yearly profits of three companies and the second view is the profits of divisions of the three companies

Capture.JPG

Capture2.JPG

This may not be possible with a single visual and using drill functionality. You should consider having multiple visuals on the same page and use the "Edit Interaction" feature to control which visuals filter and whicn ones don't. This enables you can make display prior year numbers while you are filtering/drilling current year visuals.

 

Hope this helps.

 

Check out the Chicagoland Power BI User Group

Ok gotcha - you want to put the drill path in the axis and the years in the legend - that should do it.  You can adjust what shows up in the report in the filters section.

 
Screenshot.png
Austin is VP Operations at PowerPivotPro and a professional self-service enthusiast 🙂

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.