Forum Discussion
Cumulative Total Drill through
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.
- lbendlin
Super User
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-Forum/ba-p/963216
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/1447523A 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.
- lbendlin
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.
