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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
Anonymous
Not applicable

Cumulative Total Drill through

Hey All, 

I am trying to achieve the cumulative total drill through page.

Task1 - Created a measure for cumulative sum and created a bar graph for it

Task2 - Create a drill through page and enabled the drill through page as per requirements

Task 3 - Drill through should be based on cumulative total

 

Now the problem is, lets say there are total of 100 records as of (dec, 2022) and I have 5 records in (Jan, 2023) so my running total is 105 records. Which I was able to show in the bar graph (completed task1 and task2 by here) but when I am doing the drill through on details, I am able to see just the 5 records for Jan but it should be 100 + 5 = 105 records in my drill through page.

 

Need some help on this

6 REPLIES 6
W_koepp
Frequent Visitor

I have a similar problem and cannot find a solution.  Hopefully someone here can help.

 

I have a line chart with cumulative data (i.e. 1st Qtr = 1st Qtr numbers, 2nd Qtr = 1st Qtr numbers + 2nd Qtr numbers, etc.)  Everthing works fine on the line chart and the line goes up as expected.  When I hover over the line, the automatic tool tip shows the correct cumulative value. 

 

The problem comes on the drill through that lists the individual transactions.  When I right-click on the line at the 2nd Qtr, the drill through only shows the transactions for the 2nd quarter.  (Same for the 3rd and 4th Qtrs.  The drill through only shows the transactions for that specific quarter, not the quarter and all previous quarters.)  I need it to drill through everything from the first and second quarters (i.e. <= 2nd Qtr).  I check the filters affecting the detail visual and it "is 2nd Qtr".  I need it to filter <= 2nd Qtr rather than = 2nd Qtr.  I know this is a text value and less than or equal to requires a number value.  I do have an interger column with the values 1, 2, 3, 4 for the respective quarter values that I use to sort and display the Qtr labels in the desired order on the line chart axis.  I suspect there is someway to use this interger column (Qtr#) to filter the drill-through to be <= 2 (rather than = 2nd Qtr), but I cannot figure out how to do this.

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

A simplified version of the pbix file can be found here.  The intent is to drill through from the line chart to the transaction detail for the selected and all previous quarters.

 

Here is a link to an exported csv file of what I currently get when I drill-through for the 3rd quarter for this example. I am receiving only the transactions that occurred in the third quarter.

 

Here is a link to the results that I am trying to achieve with the transactions for the current and all previous quarters. (First, second, and third quarters in this example.)

 

To complicate matters, our financial year runs October through September.  The first quarter is Oct - Dec.  So, I don't think that standard date functions would be useful because they work on a calendar year where the first quarter is Jan - Mar.

You would create a shadow copy of your Sheet1 table, and then use that in the drillthrough target page, and use TREATAS or a measure filter to selectively transfer the filters from the drillthrough.

 

lbendlin_0-1712248410421.png

 

Thank you very much.  That is exactly what I was looking for.  I cannot tell you how many manhours I spent trying to figure this out.

lbendlin
Super User
Super User

which column are you drilling through by?

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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